SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Uncommon Table Expressions


Uncommon Table Expressions

Author
Message
Greg.Gum
Greg.Gum
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 10
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
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60503 Visits: 9730
On CLR, yes, it will be faster than the CTE on that.

- 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224083 Visits: 42007
GSquared (8/11/2008)
On CLR, yes, it will be faster than the CTE on that.


Does anyone want to race? Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224083 Visits: 42007
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...

    SET NOCOUNT ON
DECLARE @BitBucket INT
DECLARE @Top INT
SET @Top = 100000
--=============================================================================
-- Recursive CTE does the count
--=============================================================================
PRINT '========== Recursive CTE =========='
SET STATISTICS IO ON
SET 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 OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)

--=============================================================================
-- ROW_NUMBER CTE does the count
--=============================================================================
PRINT '========== ROW_NUMBER CTE =========='
SET STATISTICS IO ON
SET 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 OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',100)



On my humble 6 year old 1.8Ghz P5, that code returns the following results...

========== Recursive CTE ==========
Table 'Worktable'. Scan count 2, logical reads 600001, 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 = 3375 ms, elapsed time = 4132 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 = 63 ms, elapsed time = 67 ms.
====================================================================================================

Based on that, I'd recommend not using the RBAR found in recursion for anything except the occasional hierarchy problem. BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Peter E. Kierstead
Peter E. Kierstead
SSC Eights!
SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)

Group: General Forum Members
Points: 902 Visits: 453
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 programmingWow

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!



PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60503 Visits: 9730
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? Wink


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
Greg.Gum
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 10
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
I cant let you do that Dave
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 271
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
Jeff Moden
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224083 Visits: 42007
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224083 Visits: 42007
Peter E. Kierstead (8/12/2008)
not to present a new paradigm for TSQL programmingWow


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

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search