SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Peter Kierstead / Uncommon Table Expressions / Latest Posts

RE: Uncommon Table Expressions

Hugo Kornelis — Fri, 29 Aug 2008 08:53:30 GMT

[quote][b]Jeff Moden (8/29/2008)[/b][hr]Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.[/quote]Hi Jeff,Well, I *did* write "usually"! :DOf drat, I now see I didn't :w00t:. But I did at least indicate that there have been exceptions. You can add sp_makewebtask (that I never even knew existed until I read this message :D) to that list.

RE: Uncommon Table Expressions

Jeff Moden — Fri, 29 Aug 2008 08:16:19 GMT

Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.

RE: Uncommon Table Expressions

Hugo Kornelis — Fri, 29 Aug 2008 07:39:21 GMT

[quote][b]Jeff Moden (8/29/2008)[/b][hr][quote][b]Hugo Kornelis (8/29/2008)[/b][hr]On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.[/quote]Heh... even using something that [b][i]is[/i][/b] documented is no guarantee that it won't change or get deprecated and go away.[/quote]Hi Jeff,True - but if it's documented, it'll be marked as deprecated for at least one major version before beinig really removed from the product (well, unless it's Notification Services, or English Query, or ...). If it's undocumented, it might change tomorrow, and you wouldn't know about it until you got an emergeny call from your manager... :(

RE: Uncommon Table Expressions

Jeff Moden — Fri, 29 Aug 2008 06:03:14 GMT

[quote][b]Hugo Kornelis (8/29/2008)[/b][hr]On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.[/quote]Heh... even using something that [b][i]is[/i][/b] documented is no guarantee that it won't change or get deprecated and go away.

RE: Uncommon Table Expressions

Hugo Kornelis — Fri, 29 Aug 2008 04:47:31 GMT

The article presents a nice example of a possible use for a new technique - but unfortunately, the choice of examples is a bit bad.I think every advanced SQL Server developer should make sure to know what's in his/her toolkit. And that involves a thorough knowledge of all builtin functions. All too often, that knowledge lacks, and people invent wheels that are already built into the product.Example 1 ("occurs") to count the number of characters 't' in the string: SELECT LEN(@Str) - LEN(REPLACE(@Str, 't', '')Added advantage - can also cound substrings of longer length: SELECT (LEN(@Str) - LEN(REPLACE(@Str, 'test', '')) / LEN('test')Example 2: ("phone_clean") to remove non-numeric characters:At first glance a good example. Though I agree that using a CLR function to leverage regex functionality is better (not primarly for speed, but because regex functionality is existing, tried-and-tested). Unless you have a DBA who forbids CLR functions, of course.On second look - the string concatenation technique used to piece the bits back together is undocumented, and might cause unexpected results. Not good for production code.Example 3: ("replace_Nth_char") to replace a specific character in the middle of the string: SELECT STUFF(@Str1,3,1,'0')Added advantages: (1) avoids undocumented string concatenation (see above), and (2) both replaced and replacement string can be of any length SELECT STUFF(@Str1, 12, 20, '') SELECT STUFF(@Str1, 10, 0, 'Inserted text')

RE: Uncommon Table Expressions

Jeff Moden — Tue, 12 Aug 2008 19:12:26 GMT

[quote][b]Peter E. Kierstead (8/12/2008)[/b][hr]not to present a new paradigm for TSQL programming:Wow:[/quote]Heh... sorry Peter... I just wanted to make sure no one adopted recursion as a new paradigm because of your good article. :)

RE: Uncommon Table Expressions

Jeff Moden — Tue, 12 Aug 2008 18:26:43 GMT

[quote][b]Greg.Gum (8/12/2008)[/b][hr]And then there is the CLR version that comes in with the following results on my machine:[/quote]Cool! Would you mind posting the code?

RE: Uncommon Table Expressions

I cant let you do that Dave — Tue, 12 Aug 2008 18:25:24 GMT

[quote][b]Greg.Gum (8/12/2008)[/b][Snip]CLR Recursion SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.[/quote]any chance of querying the cost column in the sys.dm_clr_appdomains before and after the clr task ?As It doesn't aggregate into statistics time CPU.I have trouble believing that interpreting MSIL and recursion could be less effort than a pre bounded CTE with no virtual method calls.see [url]http://msdn.microsoft.com/en-us/library/ms187720.aspx[/url]

RE: Uncommon Table Expressions

Greg.Gum — Tue, 12 Aug 2008 09:13:00 GMT

And then there is the CLR version that comes in with the following results on my machine:========== Recursive CTE ==========Table 'Worktable'. Scan count 2, logical reads 600002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 1375 ms, elapsed time = 1576 ms.============================================================================================================== ROW_NUMBER CTE ==========Table 'spt_values'. Scan count 2, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 15 ms, elapsed time = 26 ms.============================================================================================================== CLR Recursion ==========SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.====================================================================================================

RE: Uncommon Table Expressions

GSquared — Tue, 12 Aug 2008 08:43:46 GMT

[quote][b]Jeff Moden (8/11/2008)[/b][hr][quote][b]GSquared (8/11/2008)[/b][hr]On CLR, yes, it will be faster than the CTE on that.[/quote]Does anyone want to race? ;)[/quote]Matt's already tested that on a prior thread. A recursive CTE is slower than a good CLR on that one. Numbers table is faster than both.

RE: Uncommon Table Expressions

Peter E. Kierstead — Tue, 12 Aug 2008 08:17:55 GMT

Wow, some of these threads live a longer life than the merits of their content would seem to dictate. I posted his article to show how a new feature could be used in an unconventional way, not to present a new paradigm for TSQL programming:Wow:I agree with Jeff. Every technique should be tested before implementation, and tested again before the next implementation...If there is an art to programming then this forum is our canvas!

RE: Uncommon Table Expressions

Jeff Moden — Mon, 11 Aug 2008 17:40:45 GMT

Good article so far as the format, readability, and revelation go. The thing I'm having a problem with is the use of recursion of any kind to create a pseudo Tally table in the form of a CTE...Let's strip everything away except creating the count and have a race between the recursive method and just a flat out lookup... the @BitBucket variable is used to have a place to "dump" the count to without having the IO system or the display system get involved...[code] SET NOCOUNT ONDECLARE @BitBucket INTDECLARE @Top INT SET @Top = 100000--=============================================================================-- Recursive CTE does the count--=============================================================================PRINT '========== Recursive CTE =========='SET STATISTICS IO ONSET STATISTICS TIME ON ;WITH cteTally AS ( SELECT 1 AS N UNION ALL SELECT N+1 FROM cteTally WHERE N < @Top ) SELECT @BitBucket = N FROM cteTally OPTION (MAXRECURSION 0)SET STATISTICS TIME OFFSET STATISTICS IO OFFPRINT REPLICATE('=',100)--=============================================================================-- ROW_NUMBER CTE does the count--=============================================================================PRINT '========== ROW_NUMBER CTE =========='SET STATISTICS IO ONSET STATISTICS TIME ON ;WITH cteTally AS ( SELECT TOP(@Top) ROW_NUMBER() OVER(ORDER BY sc1.Type) AS N FROM Master.dbo.spt_Values sc1, Master.dbo.spt_Values sc2 ) SELECT @BitBucket = N FROM cteTally SET STATISTICS TIME OFFSET STATISTICS IO OFFPRINT REPLICATE('=',100)[/code]On my humble 6 year old 1.8Ghz P5, that code returns the following results...[font="Courier New"][b][color="RED"]========== Recursive CTE ==========[/color][/b]Table 'Worktable'. Scan count 2, logical reads [b][color="RED"]600001[/color][/b], physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = [b][color="RED"]3375 [/color][/b]ms, elapsed time = [b][color="RED"]4132 [/color][/b]ms.====================================================================================================[b][color="BLUE"]========== ROW_NUMBER CTE ==========[/color][/b]Table 'spt_values'. Scan count 2, logical reads [b][color="BLUE"]18[/color][/b], physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = [b][color="BLUE"]63 [/color][/b]ms, elapsed time = [b][color="BLUE"]67 [/color][/b]ms.====================================================================================================[/font]Based on that, I'd recommend not using the RBAR found in recursion for anything except the occasional hierarchy problem. :D

RE: Uncommon Table Expressions

Jeff Moden — Mon, 11 Aug 2008 17:20:10 GMT

[quote][b]GSquared (8/11/2008)[/b][hr]On CLR, yes, it will be faster than the CTE on that.[/quote]Does anyone want to race? ;)

RE: Uncommon Table Expressions

GSquared — Mon, 11 Aug 2008 15:13:26 GMT

On CLR, yes, it will be faster than the CTE on that.

RE: Uncommon Table Expressions

Greg.Gum — Mon, 11 Aug 2008 10:50:27 GMT

Does anyone consider the use of a CLR function for this type of processing?For example:[Microsoft.SqlServer.Server.SqlFunction] public static int clr_occurs2(string target, char lookFor) { int occurs = 0; for (int i = 0; i < target.Length; i++) { if (target[i] == lookFor) { occurs++; } } return occurs; }seems to be about 5 times faster than the CTE version.Greg

RE: Uncommon Table Expressions

Carl Federl — Mon, 11 Aug 2008 09:43:48 GMT

[quote]Forgive if I am new to this but does that mean that inbuilt functions don't add to reported CPU ?[/quote]CPU and IO for a UDF are NOT reported by setting statistics on.The solution that I use is to save the values from the sysprocesses table for the @@spid and then after the SQL runs, calculate the difference from sysprocesses.You will notice that there is a significant usage of CPU when a UDF is used.

RE: Uncommon Table Expressions

GSquared — Mon, 11 Aug 2008 07:08:16 GMT

The "replace Nth character" doesn't actually catch the Nth instance of a particular character, it just replaces the Nth character.Try it on "Susy sells sea shells by the sea shore.", and try using it to replace the third "s". As written, it doesn't even have an input parameter (variable) for which character to replace, nor does adding one to the case statement accomplish this.What it really does is simply replace the third character (or whichever character you use in the case statement). Since that can be done much more easily with Stuff, I don't see a purpose for that particular piece of code. If you really want to replace the third "s" in that string, here's how I'd go about it:[code]declare @String varchar(1000), @String2 varchar(1000),@Letter char(1), @Occurance int, @Replace char(1)select @String = 'Susy sells sea shells by the sea shore.', @String2 = '',@Letter = 's',@Occurance = 3,@Replace = 'X';with Chars ([Char], [Pos], Ord) as (select substring(@String, number, 1), row_number() over (partition by substring(@String, number, 1) order by number), number from common.dbo.smallnumbers where number between 1 and len(@String))select @String2 = @String2 + case when [Char] = @Letter and [Pos] = @Occurance then @Replace else [Char] endfrom Charsorder by ordselect @String2[/code]As far as parsing out a string character-by-character, a Numbers table can do that just as easily and just as fast, and can deal with arbitrarily large strings. That's mentioned in the article, but I have to ask: Why have two different functions for the same thing? If you're going to have one based on a Numbers table, why also have one based on a CTE?For the phone number and other regex type functions, speed tests have been done by members of this site, and we found that a CLR regex out-performs this CTE method. Testing and discussion at:[url]http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx[/url]The article is okay, but there are better solutions for each of these things.(In the Replace Nth CTE I built above, I reference my Common database and SmallNumbers table. I keep a database, called "Common" with a number of tables and functions that other databases reference. Much like a code library. The SmallNumbers table is 0 - 1,000.)

RE: Uncommon Table Expressions

Michael.Beeby — Mon, 11 Aug 2008 05:27:27 GMT

Those who might use 'The REPLACE_Nth_CHAR Function' might also be interested in STUFF (deletes a specified length of characters and inserts another set of characters at a specified starting point).

RE: Uncommon Table Expressions

SuperDBA-207096 — Mon, 11 Aug 2008 04:48:33 GMT

Good article!

RE: Uncommon Table Expressions

John Walker-229141 — Mon, 11 Aug 2008 04:03:58 GMT

Recursive CTE's are great for answering hierarchical questions, but usages such as this strike me as a bit lame. Despite your disclaimer, it looks as though less reasoning types will use this way beyond the narrow scope intended by the article. I'd much rather see the Database Vendor support regular expressions in a more comprehensive manner (e.g perl-compatible etc).select replace(PhoneNumber, '\D', '', 'g') as "Cleaned Phone Number" from ....... ......

RE: Uncommon Table Expressions

Atif-ullah Sheikh — Mon, 11 Aug 2008 03:47:50 GMT

Nice Article. I would definitely like to go with this technique as there are very limited scenarios where you need huge documents to get parsed character by character. I have checked this routine for about 20,000 characters and it gave results in approx. 1 sec. Quite good.Nice and informative...Atif Sheikh

RE: Uncommon Table Expressions

Anipaul — Mon, 11 Aug 2008 03:04:41 GMT

Nice article :)

RE: Uncommon Table Expressions

I cant let you do that Dave — Mon, 11 Aug 2008 01:49:40 GMT

I was surprised to see the implementation of CTE recursion (in 2005 at least) performed similarly too inbuilt functions for small string lengths (read napkin test of up to 400) for the last example compared to[code] SELECT @str2 = SUBSTRING(@Str1,1,@offset-1) + @char + Substring(@Str1,@offset+1,len(@str1)) [/code]The elapsed time was comparableThe recursive reported measurable cpu time and 2 worktable scans The inbuilt did not report anythingForgive if I am new to this but does that mean that inbuilt functions don't add to reported CPU ?The inbuilt in a scalar select didn't generate a query plan on execution; I guess it wasn't considered worth caching.

RE: Uncommon Table Expressions

ChiragNS — Mon, 11 Aug 2008 01:12:41 GMT

really liked the article...

Uncommon Table Expressions

Peter E. Kierstead — Sat, 09 Aug 2008 11:47:41 GMT

Comments posted to this topic are about the item [B]Uncommon Table Expressions[/B]