using CLR that returns table
in [Programming SQL]
|
Prev: Select Query Problem
Next: Time data type
From: Roy Goldhammer on 29 Nov 2009 09:01 Hello there. I need to build CLR which gets text and converting it to table. this CLR should run 10 times in one second. By default(as far as i know) the retuned table of CLR is saved to temp db like any other table valued function. This can cause to system to work mutch slower then it is. Is there a way to bypass this problem? any help would be useful
From: TheSQLGuru on 29 Nov 2009 09:51 Not a CLR guru here but I thought when you streamed data back to sql server as a clr 'table' it was directly consumed by the query plan. What are you actually going to do with these 10 files converted to inline tables per second queries?? -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Roy Goldhammer" <roy(a)top.com> wrote in message news:ueI7mxPcKHA.808(a)TK2MSFTNGP02.phx.gbl... > Hello there. > > I need to build CLR which gets text and converting it to table. > > this CLR should run 10 times in one second. > > By default(as far as i know) the retuned table of CLR is saved to temp db > like any other table valued function. > > This can cause to system to work mutch slower then it is. > > Is there a way to bypass this problem? > > any help would be useful >
From: Roy Goldhammer on 29 Nov 2009 10:01 I have procedure who runs approx 10 times in one second, getting huge sting with many words, I need to convert the list of these words to table where there are more then one delimiter like space, ',', ';', enter, NL and more. for example: "one two,three four five" should be 5 words. on sql server i need to go one by one and it cause me alot. ON clr there is split option which do it very fast. But as i explain it saves the data into temp table on tempdb, which cause me to work very slow. and this is what i want to bypass. can you help me on it? "TheSQLGuru" <kgboles(a)earthlink.net> wrote in message news:JO-dnQw0UqJaFI_WnZ2dnUVZ_uadnZ2d(a)earthlink.com... > Not a CLR guru here but I thought when you streamed data back to sql > server as a clr 'table' it was directly consumed by the query plan. > > What are you actually going to do with these 10 files converted to inline > tables per second queries?? > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Roy Goldhammer" <roy(a)top.com> wrote in message > news:ueI7mxPcKHA.808(a)TK2MSFTNGP02.phx.gbl... >> Hello there. >> >> I need to build CLR which gets text and converting it to table. >> >> this CLR should run 10 times in one second. >> >> By default(as far as i know) the retuned table of CLR is saved to temp db >> like any other table valued function. >> >> This can cause to system to work mutch slower then it is. >> >> Is there a way to bypass this problem? >> >> any help would be useful >> > >
From: Erland Sommarskog on 29 Nov 2009 11:03 Roy Goldhammer (roy(a)top.com) writes: > I have procedure who runs approx 10 times in one second, getting huge > sting with many words, > > I need to convert the list of these words to table where there are more > then one delimiter like space, ',', ';', enter, NL and more. > > for example: "one two,three > four > five" should be 5 words. > > on sql server i need to go one by one and it cause me alot. ON clr there > is split option which do it very fast. But as i explain it saves the > data into temp table on tempdb, which cause me to work very slow. Kevin is correct. CLR table functions are efficient, because the data is streamed into the query. There is no intermediate storage. I have however noticed that plans with CLR functions tends to include a spool operator, but I don't think this is the same thing. However, using .Split is not the best. There are people who have tried it, and experienced scaling problems. Look at http://www.sommarskog.se/arrays-in-sql-2005.html#CLR where I describe both a Split-based function and rolling your own. Also look up Adam Machanic's blog on http://www.sqlblog.com. He has a couple of more versatile suggestions that handle multi-character delimiters. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jay on 29 Nov 2009 11:13
I played with a little code and don't see why you think you can't solve this programmatically. Scanning the string with PATINDEX and using REPLACE didn't even tick on the execution timer. I think your problem may be something different. For instance, where is all this badly formatted data coming from in such quantities and duration that performance is an issue? "Roy Goldhammer" <roy(a)top.com> wrote in message news:ueI7mxPcKHA.808(a)TK2MSFTNGP02.phx.gbl... > Hello there. > > I need to build CLR which gets text and converting it to table. > > this CLR should run 10 times in one second. > > By default(as far as i know) the retuned table of CLR is saved to temp db > like any other table valued function. > > This can cause to system to work mutch slower then it is. > > Is there a way to bypass this problem? > > any help would be useful > |