Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Building a Tree Structure in an efficient and faster way. RE: Building a Tree Structure in an efficient and faster way.

  • June 30, 2015 at 7:37 am

    #1809120

    a4apple (6/25/2015)

    Hmmm... MLM. If that's true, are you using a "uni-level" payout system?

    Yes, absolutely... I saw your article Jeff.. My only question is if one account changes its parent account, all its downlines varbinary should also get updated right? is there any such query you can help me with only identifying those accounts that I have to update the Varbinary for? I think it's like asking for an extension of what you already have I guess... or is it only better to delete and reload the entire tree structure???

    Yes... updating the varbinary is easy. Just use the recursive CTE that does the initial build to do it. The filter for the primer (first SELECT) in the cte should be the ID of the person who's downline you want to rebuild. Of course, such a change will screw the Nested Sets to the floor but if you're not using those, you can just eliminate that code altogether, even from the initial rebuild.

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