Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Question

Post reply

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Jeff Moden

SSC Guru

Points: 1004340
More actions
November 15, 2012 at 12:03 am

#265432

Comments posted to this topic are about the item Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations
--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 50 total)

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

Theo Ekelmans SSCarpal Tunnel Points: 4593 More actions · Answer 1

Another excellent article, John ! This is really usefull.

Keep e'm coming 🙂

Theo

(NL)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Theo Ekelmans (11/15/2012)
Another excellent article, John ! This is really usefull.
Keep e'm coming 🙂
Theo
(NL)

Thanks, Theo. (my name isn't "John", though ;-)).

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

Michael Meierruth

SSChampion

Points: 10051

November 15, 2012 at 11:21 am

#1559465

They both start with J. He was close.:-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Another fine piece 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

JJ B SSCertifiable Points: 5130 More actions · Answer 5

Wow. Both articles are awesome. Not only is your code well documented (as usual), but the charts were also extremely useful in helping to explain the text. Well done and thanks for taking the time to really explain the methods.

adrian.facio SSCrazy Points: 2406 More actions · Answer 6

adrian.facio

SSCrazy

Points: 2406

November 15, 2012 at 3:36 pm

#1559548

Learned a lot.

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

Jeff - Working through it now but its pretty deep and I expect it will take me awhile.

Great explanations though!

I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.

Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-D

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: 1004340 More actions · Answer 8

Michael Meierruth (11/15/2012)
They both start with J. He was close.:-)

4 Letters, too! 😛

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

dwain.c (11/15/2012)
Jeff - Working through it now but its pretty deep and I expect it will take me awhile.
Great explanations though!
I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.
Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-D

Thanks, Dwain. I'd be interested in your "not quite a hierarchy" problem. Sounds interesting.

Heh... nah. Not going to change my name. Too much paper work for us all. 😀

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

adrian.facio (11/15/2012)
Learned a lot.

Thanks, Adrian. That's actually the best kind of feedback. Glad I could help.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

JJ B (11/15/2012)
Wow. Both articles are awesome. Not only is your code well documented (as usual), but the charts were also extremely useful in helping to explain the text. Well done and thanks for taking the time to really explain the methods.

Long time no "see", JJ B. Thanks for stopping by and I appreciate the feedback.

I have a secret to tell about the documentation and the charts... it was the only way I could teach myself how it would all work. :hehe: I also wanted to absolutely understand Adam's formulas so I could figure out the best way to use them and the only way I could do that was to draw some pretty pictures for myself. 😀

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

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

Jeff Moden (11/15/2012)
dwain.c (11/15/2012)
Jeff - Working through it now but its pretty deep and I expect it will take me awhile.
Great explanations though!
I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.
Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-D
Thanks, Dwain. I'd be interested in your "not quite a hierarchy" problem. Sounds interesting.
Heh... nah. Not going to change my name. Too much paper work for us all. 😀

Seems you're up might late (or real early) today...

Did I say "I'm going to?" I meant to say "I may." In any event, don't expect much from me this weekend as I'm going fishing. Need to update my avatar. 😀

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: 1004340 More actions · Answer 13

SQLRNNR (11/15/2012)
Another fine piece Jeff.

Thanks, Jason. I really appreciate it.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

dwain.c (11/16/2012)
In any event, don't expect much from me this weekend as I'm going fishing. Need to update my avatar.

Break a rod and haul that baby in! 😀 Get the gaff! Get the Gaff! {looks over the side at whats at the other end of the line}... Get the Gun! Get the Gun!

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