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

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Another excellent article, John ! This is really usefull.

    Keep e'm coming 🙂

    Theo

    (NL)

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Michael Meierruth

    SSCrazy Eights

    Points: 9991

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

    SSCarpal Tunnel

    Points: 4570

    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: 2405

    Learned a lot.

  • Dwain Camps

    SSC Guru

    Points: 86873

    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: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Dwain Camps

    SSC Guru

    Points: 86873

    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: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994537

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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