Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 11:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385490
Posted Friday, November 16, 2012 12:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
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. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385493
Posted Friday, November 16, 2012 12:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 3,618, Visits: 5,254
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.


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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385494
Posted Friday, November 16, 2012 12:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385496
Posted Friday, November 16, 2012 12:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385497
Posted Friday, November 16, 2012 12:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 536, Visits: 2,098
Jeff Moden (11/16/2012)
... it was the only way I could teach myself how it would all work.

Jeff, this is the first time I see someone use this 'terminology' as a motivating factor for writing comments. I wish more people would do it - especially in production code. Sometimes SQL code gets so convoluted when solving a hard problem that coming back to the code a year later makes you faint. And that's when you soak up the comments - and relax. I write a lot of comments like that (and not just in SQL code). Maybe I don't format it nicely like you do
Post #1385509
Posted Friday, November 16, 2012 12:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 36,943, Visits: 31,444
Michael Meierruth (11/16/2012)
Jeff Moden (11/16/2012)
... it was the only way I could teach myself how it would all work.

Jeff, this is the first time I see someone use this 'terminology' as a motivating factor for writing comments. I wish more people would do it - especially in production code. Sometimes SQL code gets so convoluted when solving a hard problem that coming back to the code a year later makes you faint. And that's when you soak up the comments - and relax. I write a lot of comments like that (and not just in SQL code). Maybe I don't format it nicely like you do


I'm right the with you, Michael. I comment even simple code. There's nothing worse than opening up, say, someone's "simple" bit of code only to find that they didn't even write a single line about what the overall purpose of the code is. Some folks say that the name of the code (proc, function, view, etc) should tell you what the code does but between some terrible naming and the need for speed when troubleshooting, well formed comments can really speed up the troubleshooting process.

Same goes for the code itself. Some say that all you need to know can be found by reading the code. I've never seen code that explains what the business reason for each SELECT, INSERT, UPDATE, DELETE, or MERGE is. It can only be done with some simple but well formed comments. It's especially helpful when you're troubleshooting someone's "simple" 2,000 line stored procedure.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1385512
Posted Friday, November 16, 2012 12:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:41 PM
Points: 536, Visits: 2,098
Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.
Post #1385515
Posted Friday, November 16, 2012 12:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 3,618, Visits: 5,254
Michael Meierruth (11/16/2012)
Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.


I'm guessing he's somewhere around Detroit but we'll see.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385516
Posted Friday, November 16, 2012 1:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Jeff,

As with the first article its top draw, though I'm still reading through them both and making the various links to previous experiences, I like the use of the rollup in the aggregation, a function that often gets overlooked.

I'm going to try and use this to redesign the hierarchy builder I have to see if I can optimise the code.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1385529
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse