[AccessD] Using the CLR with SQL Server

Sun Sep 2 22:42:37 CDT 2012

John,

One of Stuarts McLachlan's PB dll's could be fastest.
The mention of quit analysis brings Linq to mind, though.
A billion per month? I'll have to get my brother's take on that.

Your methods sound very close to address resolution and geocoding.
I can almost visualize your records falling into one of four quadrants on a
map.

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 2:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com