well, there's your problem, you're trying to do downlines recursively
to traverse a subtree for just one level down, using the adjacency model, starting at a particular node, means that you have to find all the nodes that point to that node
that's a self-join
then you have to find all the nodes that point to those nodes
if you had a multiple self-join with as many versions of the table as levels down that you wanted to go, you could get all the nodes in the subtree with one call to the database, right?
so one of the options you have is to re-program your traversal so that you retrieve "several levels down" all in one query, rather than calling the database once per node, which, if i'm not mistaken, is what happens in a recursive scheme
just an idea, i'm basically thinking out loud
in a marketing pyramid you have to deal with N levels, and i've never done that, i've only worked on hierarchies that were some maximum number of levels deep, and i just wrote one query for the entire tree (the most i ever did was eight levels -- it was a rather large self-join, i.e. the sql statement was big, but it ran really fast)
if you wrote a multiple self-join that went down M levels, where M is less than N, the maximum depth, then this might improve your performance
you wouldn't have to retrieve all the data in the tree-traversal query either, you could just retrieve the keys, their parents, and generate a "level number" that would aid in nesting (i.e. sorting) the results
then after you've called your M-level query a few times and assembled the (sub)tree of keys in an array, you could go back and fetch all the data columns in one single query, not a self-join, using a big IN list
again, just thinking out loud
the thing that's always bugged me about the celko method is that i could never figure out how to do simple things like move a node -- and its subtree! -- to another parent
the multilevel marketing scheme is mostly based upon 8 levels from parent to 8-th level child. but the whole tree is a few hundred levels deep overall.
one of the things we do is calculate commissions per rep up to 8 levels deep. so i think your approach might help us a bit.
at the end of each month, we run a full commission for the full tree and it takes 8 hours. The first rep's run of 8 levels takes 11 mins to calculate...then we move on to all of his 1st levels, run the same 8 level query to calc comms, then go to his level 2, run all calcs on level 2s, then 3s, etc.
So to pull an 8 level report given a rep, i think you're advice will help. but man, when we have to calc the whole tree, we're at 8 hours and climbing!
right now we have 2000 reps, and 40k+ customers. we anticipate 3000 reps, 120k+ customers this year. Our experience is that the commission report calculation time increases exponentially...and our fear is that when we do hit 3000 reps that it will take 16-20 hours or more just to calculate month end commissions.
SO there's got to be a better way. We've recently put 4 pentium iv xeon processors, 4gb ram, ultra360 mirrored drives into the picture and the calc times are the same (roughly speaking). SO it's obvious to me that it is our 1) architecture, and 2) our recursion that is killing performance.
I've never worked for Amway, or Mary Kay, or any other MLM group, but somebody else has to have figured this db architecture problem out.
just learning about these new models myself. frankly, my background uses eitther 1) hard coding number of levels into the db architecture, or 2) using adjacency model.
I've only just begun to toy with celko's nested sets. not convinced that its right for this siutation...though there definitely seems to be some advantages to it (easily get all children of a parent by selecting records where left values > parents_left, and right values are <parents_right. bamb...bring back all children lickety split! but how to get just eight levels of children?
i was talking with a co-developer today. others must've solved this problem...it's just a matter of finding their solution from a db schema/architetcture problem.
Also, I'm learning that SQL 2000 from Microsoft leaves a lot to be desired. other db platforms have hierarchy functions built in.
ANwyas...great thoughts you had there, gonna explore some of your ideas!
I never had tree big like this.... and I don't need to tell you that you have to try more ways....
all of us probably agree that the the best way is to have
this system allows you to move easily sub-tree somewhere else. If you will keep all the moves and deletes on SQL server (using stored procedures and triggers), then you will enjoy smooth (maybe not fastest) and client independent solution....
I have been looking at these sorts of problems too, in mapping a complex medical curriculum. I have found the following article very useful and have started to implement something along the line shown.