February 10, 2015 at 11:23 am
Hello Experts!
I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.
My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents. Could someone shed some lights or give some suggestion what could be the problem?
Thanks in Advance!
select * from SourceTable -- produces 22461 records
--step 1: first, get row number of child records in each parent
SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;
--verify step 1 produce correct result
select ParentID,ChildID, Num from #RelationshipTmp
--step 2 start conversion process
;WITH Paths(NodeID, ParentID, ChildID)
AS(
--get top most level record with no parent, denoted by parent id= empty guid
SELECT HIERARCHYID::GetRoot() as NodeID, ParentID, ChildID
FROM SourceTable
where ParentID = '00000000-0000-0000-0000-000000000000'
UNION ALL
--get path for each child
SELECT CAST(P.NodeID.ToString() + CAST(C.Num AS VARCHAR(30)) +'/' AS HIERARCHYID), c.ParentID, C.ChildID
FROM #RelationshipTmp AS C
INNER JOIN Paths P ON C.ParentID=P.ChildID
)
select NodeID.ToString(), * from Paths
February 10, 2015 at 1:43 pm
Alright! After spending another couple hours with a magnified glasses and couple packs of cigarettes I found the source of the issue. Those children nodes with multiple parents is the problem of the problem. Ironically the same is almost true in real life.
A child with multiple parents, NOT ONLY it creates multiple paths to itself, but also caused multiple paths to the nodes it is parent of (directly or indirectly).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply