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

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

  • Another excellent article, John ! This is really usefull.

    Keep e'm coming 🙂

    Theo

    (NL)

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

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

  • 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

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

  • Learned a lot.

  • 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

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

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

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

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

  • 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

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

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

Viewing 15 posts - 1 through 15 (of 50 total)

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