Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 15, 2012 11:59 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:26 PM Points: 42,082, Visits: 39,477
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1385490
 Posted Friday, November 16, 2012 12:03 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:26 PM Points: 42,082, Visits: 39,477
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1385493
 Posted Friday, November 16, 2012 12:03 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1385494
 Posted Friday, November 16, 2012 12:05 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:26 PM Points: 42,082, Visits: 39,477
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1385496
 Posted Friday, November 16, 2012 12:07 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:26 PM Points: 42,082, Visits: 39,477
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1385497
 Posted Friday, November 16, 2012 12:38 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, November 16, 2016 11:51 PM Points: 571, Visits: 2,502
 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-Forever Group: General Forum Members Last Login: Today @ 8:26 PM Points: 42,082, Visits: 39,477
Post #1385512
 Posted Friday, November 16, 2012 12:53 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, November 16, 2016 11:51 PM Points: 571, Visits: 2,502
 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 Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1385516
 Posted Friday, November 16, 2012 1:27 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 31, 2016 11:11 AM Points: 1,076, Visits: 3,229
 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

 Permissions