Geek asks and answers

CTE vs. T-SQL to determine the depth of an object hierarchy

I have a table consisting of 70,000 rows and two columns (both VARCHAR(16)): idand parent_id.

I would like to populate the "depth" column, which shows how much a particular record relates to the "root" node.

eg.

id,parent_id,depth
A,NULL,0
B,A,1
C,A,1
D,B,2
E,D,3

and etc.

I started by writing a query based on this answer on a similar question:

WITH myCTE(id, depth) AS
(
    SELECT id, 0 FROM objects where id = 'A'
    UNION ALL
    SELECT objects.id, depth + 1 FROM myCTE JOIN objects ON objects.parent_id = myCTE.id
)
SELECT id, depth FROM myCTE

In my dataset (~ 80,000 rows) the above runs for almost two hours!

Then I wrote my query as a loop and got much better performance:

ALTER TABLE objects ADD depth INT NULL
DECLARE @counter int
DECLARE @total int
SET @counter = 0
UPDATE objects SET depth = 0 WHERE id = 'A'

SELECT @total = COUNT(*) FROM objects WHERE depth IS NULL

WHILE (@total > 0)
BEGIN
    UPDATE objects SET depth = @counter + 1 WHERE parent_id IN (
        SELECT id FROM objects WHERE depth = @counter
    )
    SELECT @total = COUNT(*) FROM objects WHERE depth IS NULL
    SET @counter = @counter + 1
END

The above code takes only a couple of minutes (and it has the advantage of adding results to an existing table)

, CTE -, , ? , ? ( )

+5
2

parent_id. CTE ( spool, - LIFO)

parent_id . .

( ), . , , , .

+8

HierarchyID? .

CREATE TABLE Groups.tblHierarchyNode
(
        NodeID              Int IDENTITY (0,1),
        NodeHID             HierarchyID NOT NULL,   -- DB Hierarchy ID of where I am in a tree
        HierarchyLevel      AS NodeHID.GetLevel(),  -- Numerical level of where I am in tree
)

. , - , , , ..

0

More articles:


All Articles