Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets RE: Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

  • November 12, 2012 at 9:29 pm

    #1558374

    Just like the old cartoon, I've got to say... "Ah, Magoo! You've done it again!"

    Here are the run results using the same machine I used in testing for the article for a million nodes using your fine addition.

    Building the initial table and SortPath...

    There are 1000000 rows in dbo.Hierarchy

    Cumulative Duration = 00:00:14:420

    Building the Nested Sets...

    1000000 rows have been updated to Nested Sets

    If the rowcounts don't match, there may be orphans.

    Cumulative Duration = 00:00:42:023

    Building the indexes...

    Cumulative Duration = 00:00:47:330

    ===============================================

    RUN COMPLETE

    ===============================================

    In whole numbers, that a rock solid 13% improvement. Well done, Magoo!

    My only question now is, is it the "Identity Hack" that did it or is it the fact that you used a While Loop to replacce the rCTE (and we've previously proven that such loops will frequenctly beat rCtEs). I'll give it a try tomorrow.

    Speaking of that, tomorrow is only a half hour a way so I need to hit the hay or the people at work are going to have to put up with a really cranky ol' DBA tomorrow. 😛 Thanks again for the code, Magoo.

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