July 6, 2010 | SQL Server

Welcome to the jungle, CLR!

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!

7 comments on this post

    • Michael J Swart - July 7, 2010, 2:24 AM

      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…

    • Michael K. Campbell - July 7, 2010, 5:33 AM

      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)

    • Uri Dimant - July 7, 2010, 8:47 AM

      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

    • Uri Dimant - July 7, 2010, 8:47 AM

      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

    • Zack Jones - July 7, 2010, 2:43 PM

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

    • Emito - July 7, 2010, 8:16 PM

      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

    • Kevin Boles - July 7, 2010, 8:52 PM

      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

Comments are closed.