[AccessD] Using the CLR with SQL Server

Michael,

I do a ton of SQL Server automation where I BCP files in and out, run dynamic SQL to update records, 
control processes etc.  However I have run into one case where I need to split a CSV string in one 
field of a record, and then based on the values in the string, update another field with a value. 
IOW the string has 0 to N "error codes", where N is practically speaking about 8 maximum codes. 
Depending on the error codes I need to create a "simpler" code to tell me how to use the record.  I 
need to do this to chunks of up to 2 million records, and tables of hundreds of millions of records. 
  String handling directly in TSQL doesn't appear to be particularly speedy so I was looking at 
building a function in C# to do this stuff, returning the "simpler code" as a function return value 
which is then assigned to the field being updated, directly in TSQL.  IOW

UPDATE [MyDB].[dbo].[MyTbl]
    SET [AddrValid] = MyCSFunction(FieldAVal, FieldBVal)
  WHERE (Whatever selection may be needed)

Currently I do this in a UDF, i.e. MyCSVFunction() is actually MyUDF() called from my dynamic TSQL, 
but the UDF does not do very thorough error code analysis, and was designed back when I was doing a 
total of a couple of hundred million records a month.  I am now pushing a billion records a month 
and climbing, so I am trying to rethink my existing solution.  Essentially each record update, even 
though done in SQL, is evaluating a UDF in the process and I want to replace that UDF (if possible) 
with a faster and more powerful C# solution.

I have a table of these error codes which I just created, and there are 60 of them.  Some are fatal, 
some inconvenient, some irrelevant.  Ideally I would do an exhaustive analysis of each and every 
code in the CSV string against the table to determine the "most severe error" if you will and how 
that impacts the use of the record.  As it is I kinda punt and look for specific codes and ignore 
everything else... not a good solution, but I am just not able to program an efficient TSQL solution 
to this problem.

I am not sure that I can program an efficient CLR solution either for that manner but I stand a much 
better chance I think.  However I have never tried to use the CLR so there is the learning curve as 
well.

I would like to something like:

1) Load the error table one time into a table in C#.
2) Have the function parse the CSV string into a table each call and then join to the Error table 
(in memory).
3) Order by severity desc
4) Return Max(Severity)

Or something similar.

Or...

1) Load the error table into a dictionary one time
2) Have the function parse the CSV string and look up each error in the dictionary
3) Return the severity of the most severe error code found
4) Short circuit (quit analysis) if more than a certain severity found

Or something similar.

I could of course do the whole thing in a UDF but I have never discovered how to do debugging in 
TSQL equivalent to the C# / visual studio environment.

This CSV error string just has me hamstrung in terms of correctly processing the errors, at least 
efficiently enough to handle millions of records in a reasonable time frame.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 9/2/2012 11:50 AM, Michael Mattys wrote:
> Hi John,
>
> We've used the CLR for serializing, visualizing, and altering shapes from
> MapPoint
> using the geography capabilities in SQLS 2008.
> What are you going to be using it for?
>
> Michael R Mattys
> Mattys Consulting, LLC
> www.mattysconsulting.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Sunday, September 02, 2012 12:57 AM
> To: Sqlserver-Dba; Access Developers discussion and problem solving; VBA
> Subject: [AccessD] Using the CLR with SQL Server
>
> Just a poll to see if anyone on the list is using the CLR in SQL Server.  I
> do a lot of C# work now and mostly to automate SQL Server.  I have not been
> using the CLR in SQL Server but I am ready to figure it out.
>
> Anyone?
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>