Exploring Recursive CTEs by Example

Question

Exploring Recursive CTEs by Example

Viewing 13 posts - 31 through 42 (of 42 total)

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

david.gugg SSCertifiable Points: 6096 More actions · Answer 1

Fun article, this takes me back to my college days studying math and economics. I really wish I knew everything about SQL Server then that I do now - I would have gotten better grades. Great job!

EDIT: By the way, what kind of fish is that you're holding Dwain?

Be still, and know that I am God - Psalm 46:10

jerict Newbie Points: 2 More actions · Answer 2

Excellent article. The last example still has my head spinning some. I did notice a data problem in the #edges table not matching the diagram. You'll see it if you follow the most expensive route.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

David McKinney (7/17/2012)
I love it! Especially the conclusion.
It's time we stopped being afraid to whisper the words 'Recursive CTE' for fear of being heard by the RBAR police! 😉
Have to go....I hear a knock at the door :w00t:

Heh... that would be me knocking. 😀

rCTEs should be treated as anything that has loop potential. To wit, a well formed and properly written While Loop (especially one that forms a single transaction outside the loop) will frequently out perform a recursive CTE. Then there are special cases that remind us that "It Depends" should be sufficient reminder that not all that glitters is gold. For example, Paul's excellent (and I really mean that) "Distinct" method (mentioned in the article) is truly excellent with 100 times more performance than typical/classic methods but... only if there are actually a substantial number of dupes present. It doesn't do so well if few or no dupes are present. "It Depends".

Knock the "RBAR Police" if you'd like but we're on your side. 😉 Like Cursors and While Loops, there are times when rCTEs will be the "good enough", the "best" or, sometimes, the "only" solution but never just assume such a thing. Be leery of anything recursive at the T-SQL level and ask if there might be a better way. Say it with me... "It Depends".

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182911

July 25, 2014 at 1:28 pm

#1732514

Thank you Dwain for this very good article!

😎

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Thanks to all the recent responders. This article was first published a few years back and its main value I think is that it explores some non-traditional cases.

I have since sort of lost my fascination for rCTEs, but they were very interesting as an advanced topic when I was first starting out.

Early in the article I threatened to retch if anyone brought up the same tired old hierarchy traversal as a rCTE solution. Who would have thought that I'd write something about that eventually, albeit with my own take on it.

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

That article is good because it also shows a comparison vs. a loop and what Halloween protection can do to you.

Thanks also to the RBAR police for looking in and keeping people honest.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

It's funny... I didn't even see that I had responded to a 2 year old post.

Whether or not rCTEs are the right tool for a given problem or not, you put one heck of a lot of work into that article and I enjoyed the usage on some of the math problems. Well done.

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

David McKinney SSChampion Points: 10472 More actions · Answer 7

Jeff Moden (7/25/2014)
David McKinney (7/17/2012)
I love it! Especially the conclusion.
It's time we stopped being afraid to whisper the words 'Recursive CTE' for fear of being heard by the RBAR police! 😉
Have to go....I hear a knock at the door :w00t:
Heh... that would be me knocking. 😀

I'll come quietly! 2 years waiting for that knock - do you know what that can do to a man! :crazy:

And just when you think you're in the clear, there it comes, out of nowhere. :w00t:

Still, I'm honoured it came from the Chief Constable himself! 😉

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

David McKinney (7/28/2014)
Jeff Moden (7/25/2014)
David McKinney (7/17/2012)
I love it! Especially the conclusion.
It's time we stopped being afraid to whisper the words 'Recursive CTE' for fear of being heard by the RBAR police! 😉
Have to go....I hear a knock at the door :w00t:
Heh... that would be me knocking. 😀
I'll come quietly! 2 years waiting for that knock - do you know what that can do to a man! :crazy:
And just when you think you're in the clear, there it comes, out of nowhere. :w00t:
Still, I'm honoured it came from the Chief Constable himself! 😉

BWAAA-HAAA!!!! Too funny! Thanks for a great start to a Monday morning.

And, definitely, no worries. You're one of the good guys! 🙂

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

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 9

Excellent article---well-written, a pleasure to read, and a great mix of practical and theoretical examples!!!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Jeff Moden (7/28/2014)
It's funny... I didn't even see that I had responded to a 2 year old post.
Whether or not rCTEs are the right tool for a given problem or not, you put one heck of a lot of work into that article and I enjoyed the usage on some of the math problems. Well done.

Thanks Jeff!

While it was written a couple of years ago, I still have fond memories of coming up with and trying out original examples.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 11

Aye, I also noticed it was a bit old, but still relevant.

I use recursive CTE's in market attribution quite a bit with user events (logs).

Attribution is the process of identifying a set of user actions (“events”) that contribute in some manner to a desired outcome, and then assigning a value to each of these events. Marketing attribution provides a level of understanding of what combination of events influence individuals to engage in a desired behavior, typically referred to as a conversion

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

xsevensinzx (7/29/2014)
Aye, I also noticed it was a bit old, but still relevant.
I use recursive CTE's in market attribution quite a bit with user events (logs).
Attribution is the process of identifying a set of user actions (“events”) that contribute in some manner to a desired outcome, and then assigning a value to each of these events. Marketing attribution provides a level of understanding of what combination of events influence individuals to engage in a desired behavior, typically referred to as a conversion

That sounds like an interesting user requirement.

Perhaps you could elaborate with some DDL, sample data and an example query?

To quote Jeff Moden's signature:

"Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 13

dwain.c (7/29/2014)
xsevensinzx (7/29/2014)
Aye, I also noticed it was a bit old, but still relevant.
I use recursive CTE's in market attribution quite a bit with user events (logs).
Attribution is the process of identifying a set of user actions (“events”) that contribute in some manner to a desired outcome, and then assigning a value to each of these events. Marketing attribution provides a level of understanding of what combination of events influence individuals to engage in a desired behavior, typically referred to as a conversion
That sounds like an interesting user requirement.
Perhaps you could elaborate with some DDL, sample data and an example query?
To quote Jeff Moden's signature:
"Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."

I don't know if I even can. In attribution, how you achieve attribution is the bread and butter.

I will say that attribution, like the quoted description, is about finding consumer actions and connecting the dots. This creates a holistic view of the consumer journey to a goal like a sale. As you could imagine, connecting, analyzing and predicting those dots can be pretty complex, especially when analyzing journeys that have happened and have yet to happen.