using CLR that returns table
in [Programming SQL]
|
Prev: Select Query Problem
Next: Time data type
From: Michael Coles on 30 Nov 2009 00:33 I just verified that both of Erland's links work fine. You should check your AVG and make sure you have the most up-to-date definitions. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Jay" <spam(a)nospam.org> wrote in message news:eiQ7rqUcKHA.1652(a)TK2MSFTNGP05.phx.gbl... > Erland, > > When I click on the first link, my AVG Anti-Virus goes nuts. You may have > been hijacked. > > Jay > > > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CD2E8B1450AYazorman(a)127.0.0.1... >> Jay (spam(a)nospam.org) writes: >>> I was expecting T-SQL, in part, because I've never heard of SQLCLR. BOL >>> seems a tad vague, so could you tell me the entry point for running this >>> script? >> >> You've never heard of SQLCLR? Gee, have you been a looong vacation for >> the >> last four years or so? >> >> OK, excuse me for the sarcasm, but SQL 2005, that was released four years >> ago, permits you to create objets implemented in .Net languages such as >> C# or VB .Net. You can create stored procedures, functions, triggers, >> aggregates and types in the CLR. >> >> On my web site, I can offer these introductions: >> http://www.sommarskog.se/dyn-search-2005.html#CLR >> http://www.sommarskog.se/arrays-in-sql-2005.html#CLR >> These are necessarily not the best introductions to write CLR code in >> SQL Server, but they should be enough to get the examples going. >> >> >> -- >> 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: Michael Coles on 30 Nov 2009 00:35 How big is the "huge string"? Is it less than 4,000 characters, 8,000 characters, or more? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Roy Goldhammer" <roy(a)top.com> wrote in message news:%23ZRkJTQcKHA.4724(a)TK2MSFTNGP05.phx.gbl... >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 30 Nov 2009 03:35 Jay (spam(a)nospam.org) writes: > When I click on the first link, my AVG Anti-Virus goes nuts. You may have > been hijacked. Actually, my anti-virus/Firewall/etc also started to complain on several pages on my web site during the weekend. Hm... -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Roy Goldhammer on 30 Nov 2009 03:56 Whell Jay. the reason i want the split mend for few reasons: 1. when i using Replace i can replace only one string with another, while on split i can choose more then one char to set the split. 2. to turn string to table i need loop on sql to do which i don't need on split 3. in case there are more then one space between two words i need also ltrim, rtrim which can be solved easily by CLR. As i understand from Erland CLR table-Based function does not create temp table and store the data in it "Jay" <spam(a)nospam.org> wrote in message news:OJgMq7QcKHA.2160(a)TK2MSFTNGP02.phx.gbl... >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 >> > >
From: Michael Coles on 30 Nov 2009 10:21
> 2. to turn string to table i need loop on sql to do which i don't need on > split Actually you don't need a "loop on sql" to split a string. You can do it with an inner join to a numbers table. You can also use more than one character as a split character. A lot of examples of this can be found in this group and by doing a Google search ("SQL Server split string"). "Roy Goldhammer" <roy(a)top.com> wrote in message news:eZ4EasZcKHA.4724(a)TK2MSFTNGP05.phx.gbl... > Whell Jay. > > the reason i want the split mend for few reasons: > 1. when i using Replace i can replace only one string with another, while > on split i can choose more then one char to set the split. > 2. to turn string to table i need loop on sql to do which i don't need on > split > 3. in case there are more then one space between two words i need also > ltrim, rtrim which can be solved easily by CLR. > > As i understand from Erland CLR table-Based function does not create temp > table and store the data in it > "Jay" <spam(a)nospam.org> wrote in message > news:OJgMq7QcKHA.2160(a)TK2MSFTNGP02.phx.gbl... >>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 >>> >> >> > > |