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


Hidden RBAR: Counting with Recursive CTE's


Hidden RBAR: Counting with Recursive CTE's

Author
Message
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 2396
Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now.... :-)
drew.georgopulos
drew.georgopulos
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 705
Good God i had no idea reading could make me feel so smart!...now it just has to sink in
Thank you very much for explaining the intracacies of each path.
drew
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66361 Visits: 18570
Great Article Jeff.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Sir Slicendice
Sir Slicendice
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 315
Nice article - but I will mention: recursive CTE's aren't always slow! In fact, in my study of the capabilities of the optimizer a few years back, I found that the optimizer was definitely able to properly turn many tail-recursive CTE's into loops, making them just as fast - and far more transparent - than hand writing the loop. I don't have time to dig out code now, but it's pretty easy to test on your own: the key is to make the CTE properly tail-recursive, and keep it simple. The optimizer is definitely incapable of tail recursion optimization across functions, though.

But just because it's recursive doesn't necessarily mean it's slow!
-frank


The End.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
terrance.steadman (8/8/2011)
Greetings Jeff,

That was a wonderful and very insightful article about rCTE's and ways to count out of the box. I will have to put some of the pseudo-counters into practice in my scripts.

Thank you for sharing.




P.S.

Beer popsicles are possible if you know the *secret* ingredient to mix to make it become solid. :-)



Heh... yeah... the ol' "hot ice" trick works pretty well but makes the beer taste pretty bad. That's why I use the "Chill'n'Tap" method, instead. :-P

Thanks for stopping by and for the feedback. Hmmmm. "Pseudo-Counters". You may have just coined your own term for the 3 counting methods in the article that use "Pseudo-Cursors". :-)

--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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
Sir Slicendice (8/8/2011)
But just because it's recursive doesn't necessarily mean it's slow!
-frank


Nor did I say so in the article. ;-) I absolutely agree and that's why I limited the slowness problem to these types of "counting" rCTE's. Even the title of the article reflects that fact.

It is good, though, to emphasize the fact that I was only talking about Counting rCTE's in the article, so thanks for bringing it up, Frank. I have to admit , though... although While Loops and rCTE's take turns winning, I generally try to avoid both in favor of set-based solutions.

--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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
drew.georgopulos (8/8/2011)
Good God i had no idea reading could make me feel so smart!...now it just has to sink in
Thank you very much for explaining the intracacies of each path.
drew


Heh... sorry about the very slow lead-in on Pseudo-Cursors. Drew. At the risk of causing some of the more experienced users to nod off, I wanted to make absolutely sure that even a total Neophyte to SQL would understand the idea.

Thanks for stopping by and thanks for the feedback.

--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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
CELKO (8/8/2011)
This was really good. I was surprised that the WHILE loop did so better than the rCTE. I was expecting the loop to be a bit better because it does not have to touch a table except to write an integer, but I expected the optimizer to turn the rCTE into a loop.


Thanks for stopping by and for the feedback, Joe. I think I'm regretting not including a While Loop in the coded demonstration in the article. Maybe I'll go back and add it in along with the results in the graphs, as well. I've just got to find the time to do that.

Shifiting gears, how's the 2nd edition of "Trees" coming along?

--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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
mcannistra (8/8/2011)
Thank you Jeff for your wonderful post.
It has been extremely helpful to achieve faster speed in a test case I was creating on sql azure.
I referenced your post in the Microsoft forum where I had already started a thread before finding your precious info:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c368315a-1bf1-4353-b0af-c4cfbb44b2c1

Cheers,
Mario


Thanks for the read and the feedback, Mario. Just remember... I didn't invent any of these methods (they've all been around for a very long time) especially the Cascading CTE method. That's Itzik Ben-Gan's. The only thing I did differently was to include some optimizations that a bunch of us here at SSC have added over time, one of which is the Base 10 counts. Itzik used Base 2 in his and, IIRC, was also the first to use the "TOP" optimization.

And thanks for the mention on the MSDN forum. I'm glad you could put one of the methods to good use so quickly.

--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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214120 Visits: 41979
Kenneth Wymore (8/8/2011)
Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now.... :-)


BWAA_HAAA!!! It'll be a "Fire Drill" for sure, Kenneth. :-)

As Frank pointed out in a post above, not all rCTE's are bad. The article was only concerned with "Counting" rCTE's. It's worthwhile to to compare methods before deciding which method to put into production. Usually, I avoid rCTE's altogether simply because I can usually find or build a bit a set based code but I do test rCTE solutions as well as others in my "need for speed". :-)

Thanks for the read and the feedback.

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