Welcome to the jungle, CLR!
July 6th, 20107
Welcome to the jungle, CLR!
July 6th, 20107
 
 

I've always been very reluctant to use the CLR. On a completely unrelated tangent recently, I begrudgingly threw CLR into the ring of a string splitting performance test (blog post forthcoming). Actually, the reason I was performing the test at all was because of a couple of blog posts by Brad Schulz, where he shows some great enhancements to typical XML splitting mechanisms. (Articles are here and here.)

The results were astounding to me. Again, I will provide far more details later, but I was convinced that the mere overhead of the CLR would make it slower than other solutions, particularly on smaller strings. (Think about how hard it is to shave 5 minutes off of a 20 minute drive.) I was surprised that this was not the case at all, and as a result, I will be testing various ways to implement the CLR for various string functionality, such as splitting strings, and of course validating e-mail addresses and other RegEx necessities. I will also be interested to test it for file system operations, such as browsing a directory list, and queuing files up for BULK INSERT / archive / destruction.

So, CLR, you haven't yet found a place in my heart, but you have found a place in my databases. And it is due in large part to Adam Machanic, whose post last April on splitting strings has become one of my new favorites.

I hope this will be enough motivation for all of you other holdouts out there to give the CLR a shot!

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

7 Responses

  1. Kevin Boles says:

    Aaron, I recommend you review this amazing thread from SQLServerCentral:  http://www.sqlservercentral.com/Forums/Topic695508-338-6.aspx
    There is an incredible range of solutions on the split string problem with all kinds of benchmarks and sample code (including stuff from Adam Machanic).  
    Kevin Boles

  2. Emito says:

    Michael, in Oracle you could use VARRAY and NESTED TABLE.
    Take a look at this link:
    http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/ch19_01.htm

  3. Zack Jones says:

    We are jumping on the CLR bandwagon here as well so any hints/tips would be greatly appreciated.

  4. Uri Dimant says:

    Hi Aaron
    I am about to implement almost the same (string manipulation) on our server, will be waiting for  you next post for more details..
    Thanks

  5. Uri Dimant says:

    Hi Aaron
    I am about to implement almost the same (string manipulation) on our server, will be waiting for  you next post for more details..
    Thanks

  6. Michael K. Campbell says:

    Interestingly enough, the 'overhead' of the CLR is ALWAYS in play. i.e. the CLR is ALWAYS loaded into memory and running. You just have to configure it for ALLOWED use by your code/etc. (but doing that doesn't magically load it into memory/etc)

  7. Michael J Swart says:

    Each sql-server specific thing we let in our dbs is one more step away from our dream of one day supporting other platforms.
    That's one of the reasons that we (as a vendor) avoid too many MS-specific features like CLR (I know that xml datatypes are no different in that respect).
    Out of curiosity I wonder if anyone has written an Oracle equivalent to this: http://www.sommarskog.se/arrays-in-sql-2005.html
    I'd like to know what is supported in terms of arrays and lists in Oracle. That's a question for another forum…