Developing Matt

My Technical Journal

SPLIT with CLR

leave a comment »

So I decided to take Adam Machanic and Itzik Ben-Gan at their word.  The other day I took a course by Adam Machanic regarding whether to use t-sql or clr.  And about a year ago I took a course by Itzik where he reviewed the different ways to split a comma separated list of words into a table (this can also be found in a whitepaper titled Arrays and Lists in Sql Server 2005). 

Itzik said that it would indeed be best (for performance) to write a clr routine.  Machanic himself tested that when just doing a simple charindex versus an indexof (in a clr routine) that clr performed 3x better when the length of characters exceeded 50,000 characters.

So, having never written a clr-anything I decided to give it a try, depending heavily on this article.  There’s a new ‘SQL SERVER PROJECT’ option in visual studio 2008, but I didn’t know what it was…so I stayed away from it.  Instead I just wrote a class affectionately named ‘fnParseString’ with the code from the article. 

I compiled the code.

I turned on the thing that needed to be turned on for my server

sp_configure ‘clr enabled’, 1
GO
RECONFIGURE

I then created an assembly

CREATE ASSEMBLY CLRFunctions FROM ‘C:\tempsql\fnParseString.dll’

When I created a function with the article it didn’t work, telling me that it couldn’t find my UserDefinedFunction.  After many iterations I ended up with the below external name (in italics below).  Not sure how to rectify this one in the future, but one article I read suggested that it might be due to the fact that I picked just a class project.

CREATE FUNCTION [dbo].fnParseStringCLR(@string [nvarchar](4000), @separator [nchar](1))
RETURNS TABLE (
[StringCol] [nvarchar](max) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME CLRFunctions.[fnParseString.UserDefinedFunctions].parseStringCLR

Then I called it and compared to the next best thing..the function we use at my company (a function that uses a numbers table) the performance was significant.  It was a 50% savings to use CLR.   WOW!

 

—edit 9/16/09 3:45pm

In looking into the security concerns with CLR I found a great article by Don Kiely here.  Two things I picked up from this article.  CLR doesn’t have any more security concerns than t-sql within the same security context, but it does have possible memory issues with poorly written code…which can be the same problem with some of those nastily written t-sql procedures that you continue to ignore.  : )

Advertisements

Written by matt

September 16, 2009 at 4:11 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: