Hidden RBAR: Counting with Recursive CTE's

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Hidden RBAR: Counting with Recursive CTE's

69 posts, Page 2 of 7

««1 2 3 4 5 »»»

Hidden RBAR: Counting with Recursive CTE's

Rate Topic

Display Mode

Topic Options

Author

Message

KWymore

Posted Monday, August 08, 2011 10:56 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 2,602, Visits: 1,553

Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now.... Smile

Post #1156241

drew.georgopulos

Posted Monday, August 08, 2011 12:01 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 310, Visits: 445

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

Post #1156289

SQLRNNR

Posted Monday, August 08, 2011 12:05 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

Great Article Jeff.

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1156295

Sir Slicendice

Posted Monday, August 08, 2011 12:14 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 39, Visits: 296

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.

Post #1156305

Jeff Moden

Posted Monday, August 08, 2011 12:21 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

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

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1156312

Jeff Moden

Posted Monday, August 08, 2011 12:28 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

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

Post #1156318

Jeff Moden

Posted Monday, August 08, 2011 12:31 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

Post #1156319

Jeff Moden

Posted Monday, August 08, 2011 12:35 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

Post #1156324

Jeff Moden

Posted Monday, August 08, 2011 12:41 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

Post #1156328

Jeff Moden

Posted Monday, August 08, 2011 12:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

BWAA_HAAA!!! It'll be a "Fire Drill" for sure, Kenneth. Smile

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". Smile

Thanks for the read and the feedback.

--Jeff Moden

Post #1156335

« Prev Topic | Next Topic »

69 posts, Page 2 of 7

««1 2 3 4 5 »»»

Permissions