Hierarchical Tree Reading in SQL Server

Question

Post reply

Hierarchical Tree Reading in SQL Server

a4apple

SSCrazy

Points: 2620
More actions
June 8, 2015 at 4:31 pm

#318058

Hello All,
Currently we have a Hierarchial Tree table that saves our account level information as follows.
sample DDL
CREATE TABLE TreeTable
(
AccountID INT,
SponsorID INT,
[Level] INT,
[Tree] VARBINARY(4000),
CONSTRAINT PK_TreeTable PRIMARY KEY (AccountID, SponsorID, Level)
)
sample DML
INSERT INTO TableTree (AccountID, SponsorID, [Level], [Tree])
VALUES (2, NULL, 1, '0x00000002'), (1490, 2, 2, '0x00000002000005D2'), (1496, 1490, 3, '0x00000002000005D2000005D8')
We deal with 1.5M to 2M records at any given time. Right now It takes 2-3 Minutes to load this data. We use the Varbinary field to move upwards / downwards in the tree at any given time.
Now, I want to change the Varbinary field to a Varchar field to save data load time. (just thinking)
sample DDL
CREATE TABLE NewTreeTable
(
AccountID INT,
SponsorID INT,
[Level] INT,
[Tree] VARCHAR(4000),
CONSTRAINT PK_NewTreeTable PRIMARY KEY (AccountID, SponsorID, Level)
)
sample DML
INSERT INTO NewTableTree (AccountID, SponsorID, [Level], [Tree])
VALUES (2, NULL, 1, '2'), (1490, 2, 2, '2\1490'), (1496, 1490, 3, '2\1490\1496')
Based on my NewTreeTable, I want to find out who are the higher level sponsors for 1496. Is there a query that you can help me with that using this VARCHAR field. I have done the same with VARBINARY though.
Any help is appreciated. Thanks in Advance
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 1

Try using Jeff Modens string splitter

http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT s.Item AS Sponsor

FROM NewTreeTable n

CROSS APPLY dbo.DelimitedSplit8k(n.Tree,'\') s

WHERE n.AccountID = 1496

AND s.Item <> n.AccountID;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

a4apple (6/8/2015)
Hello All,
Currently we have a Hierarchial Tree table that saves our account level information as follows.
sample DDL
CREATE TABLE TreeTable
(
AccountID INT,
SponsorID INT,
[Level] INT,
[Tree] VARBINARY(4000),
CONSTRAINT PK_TreeTable PRIMARY KEY (AccountID, SponsorID, Level)
)
sample DML
INSERT INTO TableTree (AccountID, SponsorID, [Level], [Tree])
VALUES (2, NULL, 1, '0x00000002'), (1490, 2, 2, '0x00000002000005D2'), (1496, 1490, 3, '0x00000002000005D2000005D8')
We deal with 1.5M to 2M records at any given time. Right now It takes 2-3 Minutes to load this data. We use the Varbinary field to move upwards / downwards in the tree at any given time.
Now, I want to change the Varbinary field to a Varchar field to save data load time. (just thinking)
sample DDL
CREATE TABLE NewTreeTable
(
AccountID INT,
SponsorID INT,
[Level] INT,
[Tree] VARCHAR(4000),
CONSTRAINT PK_NewTreeTable PRIMARY KEY (AccountID, SponsorID, Level)
)
sample DML
INSERT INTO NewTableTree (AccountID, SponsorID, [Level], [Tree])
VALUES (2, NULL, 1, '2'), (1490, 2, 2, '2\1490'), (1496, 1490, 3, '2\1490\1496')
Based on my NewTreeTable, I want to find out who are the higher level sponsors for 1496. Is there a query that you can help me with that using this VARCHAR field. I have done the same with VARBINARY though.
Any help is appreciated. Thanks in Advance

First, don't change the VARBINARY to VARCHAR. It WILL slow things down if what you're storing as IDs is an INT.

I also don't know what kind of processes you have but this sounds like the perfect candidate for the following two articles.

Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url]

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations[/url]

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

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

You might also have a problem with you Primary Key. In most "Adjacency List" hierarchies, the child (your AccountID) should be the single column PK as well as the Clustered Index (the default for PKs but can be changed). The child column must be unique or you could end up with a mess on your hands because each and every AccountID should have one and only one SponsorID. The ONLY way to enforce that is to make the AccountID a unique index and, considering how often searches will be done in that manner, it (IMHO) absolutely should be the PK.

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