Hidden RBAR: Counting with Recursive CTE's
-
Comments posted to this topic are about the item Hidden RBAR: Counting with Recursive CTE's
--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) -
Very insightful article. Thanks.
I have one question, how do you discard the output?
(Anticipating a "slap-to-the-forehead" moment, but asking anyway...)
Best,
Henk
-
Sorry, but there's a glaring inconsistency in this article.
No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
- what you've been eating all this time isn't really made from beer or
- there isn't really a secret formula, and you're stringing us all along.
Tut, tut, Mr Moden....
😉
Semper in excretia, suus solum profundum variat
-
Excellent, Thanks Jeff!
-
Henk van den Berg (8/8/2011)
Very insightful article. Thanks.I have one question, how do you discard the output?
(Anticipating a "slap-to-the-forehead" moment, but asking anyway...)
Best,
Henk
Heh... no problem, Henk. A lot of people miss it. In SQL Server 2005, start by selecting {Tools}{Options}{Query Results}{Results to Grid}. Then, click the {Discard results after execution} check box. In 2005, you may have to open a new query window in SSMS before the setting takes affect. In 2008, (IIRC) the setting is good for the current window and any new windows you open while the box is checked.
--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) -
majorbloodnock (8/8/2011)
Sorry, but there's a glaring inconsistency in this article.No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either
- what you've been eating all this time isn't really made from beer or
- there isn't really a secret formula, and you're stringing us all along.
Tut, tut, Mr Moden....
😉
Heh... because I like American mass-produced beer in pop-top cans, some would say that I, indeed, have no taste buds. 😛
--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) -
George J Verras (8/8/2011)
Excellent, Thanks Jeff!You bet, George. Thanks for stopping by and 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.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) -
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. 🙂
-
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:
Cheers,
Mario
-
Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now.... 🙂
-
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
-
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[/url]
Learn Extended Events -
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. -
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. 😛
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.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) -
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.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)
Viewing 15 posts - 1 through 15 (of 73 total)
You must be logged in to reply to this topic. Login to reply