Uncommon Table Expressions

Question

Uncommon Table Expressions

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Jeff Moden (8/11/2008)
GSquared (8/11/2008)
On CLR, yes, it will be faster than the CTE on that.
Does anyone want to race? 😉

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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Greg.Gum Valued Member Points: 58 More actions · Answer 2

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.

====================================================================================================

I cant let you do that Dave Right there with Babe Points: 783 More actions · Answer 3

Greg.Gum (8/12/2008)[Snip]
CLR Recursion SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.

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 http://msdn.microsoft.com/en-us/library/ms187720.aspx

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Greg.Gum (8/12/2008)
And then there is the CLR version that comes in with the following results on my machine:

Cool! Would you mind posting the code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Peter E. Kierstead (8/12/2008)
not to present a new paradigm for TSQL programming:Wow:

Heh... sorry Peter... I just wanted to make sure no one adopted recursion as a new paradigm because of your good article. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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')

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Hugo Kornelis (8/29/2008)
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.

Heh... even using something that is documented is no guarantee that it won't change or get deprecated and go away.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

Jeff Moden (8/29/2008)
Hugo Kornelis (8/29/2008)
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.
Heh... even using something that is documented is no guarantee that it won't change or get deprecated and go away.

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... 🙁

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

Jeff Moden (8/29/2008)
Even that's not true, Hugo... the security on sp_MakeWebTask changed without warning on sp4 of 2k.

Hi Jeff,

Well, I *did* write "usually"! 😀

Of 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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/