Mark Breen
marklbreen at gmail.com
Mon Sep 3 15:13:57 CDT 2012
Hello Michael Your comment intrigues me { 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. } Do you have any articles or short summary reading to explain that just a little more? Is this related to the 'Graph' database discussions we had a few weeks ago ? thanks Mark On 3 September 2012 04:42, Michael Mattys <michael at mattysconsulting.com>wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >