Post reply

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

  • November 16, 2012 at 12:38 am

    #1559651

    Jeff Moden (11/16/2012)

    ... it was the only way I could teach myself how it would all work. :hehe:

    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 😛

  • November 16, 2012 at 12:48 am

    #1559654

    Michael Meierruth (11/16/2012)

    Jeff Moden (11/16/2012)

    ... it was the only way I could teach myself how it would all work. :hehe:

    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.

    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)

  • November 16, 2012 at 12:53 am

    #1559656

    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.

  • November 16, 2012 at 12:56 am

    #1559657

    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![/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

  • November 16, 2012 at 1:27 am

    #1559670

    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

  • November 16, 2012 at 7:04 am

    #1559797

    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.

    2:48 to be precise. 🙂 And Dwain has it spot on. I live in the area of Detroit. I like to get on the forums to relax a bit after work and work frequently has me up quite late.

    We ran into a major problem with a 3rd party upgrade to their software and I had to fix that, which took a good portion of the day. Then we ran into a privs problem with another 3rd party bit of software and trying to figure out what they really needed took a good while. Of course, they wanted "SA" privs and, of course, I'm hell bent on giving them only what I think they really need. I'll never give a 3rd party "SA" privs even on a dev box. After that, I had to fix the point-in-time backups on the dev and staging boxes (seems like overkill but I protect my developers' work as much as I can). We have more disk space on order but it's not in time for the sudden growth we experiennced because of several new clients and all the major DB backups simply ran out of room.

    Then, there are the normal daily questions and special requests that need to be handled. For example, I peer review 100% of the code that moves from dev to staging and do a final check when it moves from staging to production. The peer reviews are also time to "mentor" the developers in the art of writing good, high performance SQL and time for me to keep up with all the projects so I know what's coming up for the production databases.

    None of that means that I can avoid the work that I need to do on another time-sensitive project and I didn't get started on that until quite late in the day.

    Heh... so, yes... to answer the underlying question, I have a hell of a sleep deficit going on most weeks and I'm slightly insane. I need to learn from my own joke about the difference between being "loyal" to a company and being "dedicated". You have to think about "Ham'n'Eggs".... the chicken was loyal... the pig was dedicated. 😀

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

  • November 16, 2012 at 7:08 am

    #1559800

    Jason-299789 (11/16/2012)

    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.

    "Impending usage" because of an article is one of the highest compliments someone like me could hope to have. Thank you very much, Jason. Let us know how it turns out.

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

  • November 16, 2012 at 10:17 am

    #1559877

    Jeff -

    Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.

    I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.

    - Les

  • November 18, 2012 at 10:03 am

    #1560094

    CELKO (11/18/2012)

    When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all.

    Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.

    There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. 😉 Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.

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

  • November 18, 2012 at 10:49 am

    #1560100

    Jeff Moden (11/18/2012)

    CELKO (11/18/2012)

    When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all.

    Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.

    There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. 😉 Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.

    Jeff, the problem is Mr. Celko. He thrives on the unprofessional discourse that his on-line persona causes. I really wish we could just get him to go away.

  • November 18, 2012 at 11:15 am

    #1560104

    lnoland (11/16/2012)

    Jeff -

    Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.

    I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.

    - Les

    Thanks for the geat feedback, Les. I really appreciate it.

    If you do get a chance to apply some of these things to some of the items you have at work, I'd really be interested in finding out what you did and any problems you may have had to overcome. These post-article discussions are, many times, where the real innovation occurs.

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

  • December 24, 2012 at 12:03 pm

    #1571335

    Nice article.

  • January 20, 2013 at 5:13 am

    #1579016

    Excellent article(s) Jeff!

    I emailed you to your ameritech adress last week wondering about when your "nested set killer" will be published. Sorry to say the email bounced so you will have to send me your new email.

    Otherwise, the only thing I missed was the heads up about the article being published!

    Kudos.

    //Peter

    N 56°04'39.16"
    E 12°55'05.25"

  • April 3, 2014 at 7:58 am

    #1702862

    Thank you for the excellent information! Is there a way to order the personnel in each level by something besides EmployeeID? My user wants to specify the order of the personnel in each level. For example, in level 2, instead of ordering Lynne, Bob, Ken and Marge, he wants Marge, Bob, Lynne, Ken. He wants to do this for each level. Our table has a column UICOrder (int) where the user enters the order of how he wants the items in each level to appear. Your solution in this article works for me except for the ordering of the levels. Any assistance is greatly appreciated!

    Thank you,

    Kathy Davis

  • April 3, 2014 at 8:27 am

    #1702885

    kathy.m.davis.ctr (4/3/2014)

    Thank you for the excellent information! Is there a way to order the personnel in each level by something besides EmployeeID? My user wants to specify the order of the personnel in each level. For example, in level 2, instead of ordering Lynne, Bob, Ken and Marge, he wants Marge, Bob, Lynne, Ken. He wants to do this for each level. Our table has a column UICOrder (int) where the user enters the order of how he wants the items in each level to appear. Your solution in this article works for me except for the ordering of the levels. Any assistance is greatly appreciated!

    Thank you,

    Kathy Davis

    I'll have to take a look at this after work tonight, Kathy. And, thanks for the feedback.

    --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 - 16 through 30 (of 50 total)

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