Building a Tree Structure in an efficient and faster way.

Question

Building a Tree Structure in an efficient and faster way.

Viewing 3 posts - 16 through 17 (of 17 total)

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

a4apple SSCrazy Points: 2620 More actions · Answer 1

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

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

a4apple SSCrazy Points: 2620 More actions · Answer 2

Matt Miller (#4) (6/25/2015)
here's ONE way to take on selectively "moving" certain subtrees. I am showing you a select version for you to compare - would be easy to flip it to update.
DECLARE @Tree TABLE
(
ID INT,
DateKey INT,
AccountID INT,
ParentAccountID INT,
[Level] INT,
[Structure] VARBINARY(2000)
)
INSERT INTO @Tree
SELECT 1, 201506, 2, NULL, 1 , CAST(2 AS varbinary(2000))
UNION
SELECT 2, 201506, 3, 2, 2 , CAST(2 AS varbinary(2000))+CAST(3 AS varbinary(2000))
UNION
SELECT 3, 201506, 4, 3, 3 , CAST(2 AS varbinary(2000))+CAST(3 AS varbinary(2000))+CAST(4 AS varbinary(2000))
UNION
SELECT 4, 201506, 5, 2, 2 , CAST(2 AS varbinary(2000))+CAST(5 AS varbinary(2000))
UNION
SELECT 5, 201506, 6, 2, 2 , CAST(2 AS varbinary(2000))+CAST(6 AS varbinary(2000))
UNION
SELECT 6, 201506, 7, 3, 3 , CAST(2 AS varbinary(2000))+CAST(3 AS varbinary(2000))+CAST(7 AS varbinary(2000))
UNION
SELECT 7, 201506, 8, 5, 3 , CAST(2 AS varbinary(2000))+CAST(5 AS varbinary(2000))+CAST(8 AS varbinary(2000))
UNION
SELECT 8, 201506, 9, 8, 3 , CAST(2 AS varbinary(2000))+CAST(5 AS varbinary(2000))+CAST(8 AS varbinary(2000))+CAST(9 AS varbinary(2000))
SELECT * FROM @Tree;
--set up date process
declare @baseID int;
set @baseID=4; --the node being moved
update @tree set ParentAccountID=3 where id=4;
with recurseCTE as (
SELECT basenode.ID ,
basenode.DateKey,
basenode.AccountID,
basenode.ParentAccountID,
basenode.[Level],
parent.level+1 newlevel,
basenode.[Structure],
cast(parent.Structure+cast(basenode.AccountID as varbinary(2000)) as varbinary(2000)) newstructure
FROM @tree basenode join @tree parent on basenode.parentaccountID=parent.accountid where basenode.id=@baseid and basenode.level>1
union all --rescursive part of the query
SELECT basenode.ID ,
basenode.DateKey,
basenode.AccountID,
basenode.ParentAccountID,
basenode.[Level],
parent.newlevel+1 newlevel,
basenode.[Structure],
cast(parent.newstructure+cast(basenode.AccountID as varbinary(2000)) as varbinary(2000)) newstructure
FROM @tree basenode join recurseCTE parent on basenode.parentaccountID=parent.accountid
)
select * from recursecte

Matt, Thank you so much for the code. This is something exactly I was looking for. I will look into it and Mark this as solution if I have it working. Thank you once again.

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

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)