I have already posted for the HierarchyId and CTE (Common Table Expression), also given the comparison review of them for the level and hierarchical order data. I am not saying that HierarchyId is better than CTE or CTE is better then HierarchyId. But it all depends on. You need to practically use them and review the performance of hierarchies and CTE. I am going to show one more demo to find the members with downline and upline.
1. Get down line data using CTE and HierarchyId and compare the execution plan.
2. Get up line data using CTE and HierarchyId and compare the execution plan.
Hope you like this and share your experience as comments.
-- creating database and objects
CREATE DATABASE HierarchyDB
GO
USE HierarchyDB
GO
IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab
GO
CREATE TABLE HierarchyTab
(
NodeId INT NOT NULL
,NodeParent int
,NodeDepth VARCHAR(100) NOT NULL
,NodePath HIERARCHYID NULL
,NodeLevel as (NodePath.[GetLevel]())
,NodeStringPath as (NodePath.ToString())
,NodeDesc VARCHAR(100)
)
GO
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)
GO
INSERT INTO HierarchyTab(NodeId,NodeParent,NodeDepth,NodePath,NodeDesc)
VALUES
(1,NULL,'1',HIERARCHYID::Parse('/'),'Node-1'),
(2,1,'1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
(3,2,'1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
(4,2,'1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
(5,1,'1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
(6,5,'1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
(7,5,'1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
(8,7,'1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
(9,8,'1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9')
GO
SELECT
*
FROM HierarchyTab
GO
1. Get down line data using CTE and HierarchyId and compare the execution plan.
-- Using CTE (Not Using NodePath and NodeLevel)When you run above script you have data and execution plans as follow,
;WITH cteLevels
AS
(
SELECT
NodeId as Node
,NodeParent as Perent
,NodeDepth as Depth
,0 AS [Level]
,NodeDesc as [Desc]
FROM HierarchyTab
WHERE NodeId = 1
UNION ALL
SELECT
NodeId
,NodeParent
,NodeDepth
,[Level] + 1 AS [Level]
,NodeDesc
FROM HierarchyTab
INNER JOIN cteLevels
ON ( NodeParent = Node )
WHERE [Level] + 1 < =3
)
SELECT
*
FROM cteLevels
WHERE [Level] = 3
GO
-- With HierarchyId
DECLARE @DownlineNode HierarchyId =
(SELECT NodePath FROM HierarchyTab WHERE NodeId = 1)
SELECT
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeParent,
NodeDepth,
NodeLevel,
NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(@DownlineNode) = 1
AND NodeLevel = @DownlineNode.GetLevel() + 3
GO
2. Get up line data using CTE and HierarchyId and compare the execution plan.
-- Using CTE (Not Using NodePath and NodeLevel)The result set and execution plan when you run above script
;WITH cteLevels
AS
(
SELECT
NodeId as Node
,NodeStringPath as StringPath
,NodeParent as Parent
,NodeDepth as Depth
,0 AS [Level]
,NodeDesc as [Desc]
FROM HierarchyTab
WHERE NodeId = 9
UNION ALL
SELECT
NodeId
,NodeStringPath as StringPath
,NodeParent
,NodeDepth
,[Level] + 1 AS [Level]
,NodeDesc
FROM HierarchyTab
INNER JOIN cteLevels
ON ( NodeId = Parent )
WHERE [Level] + 1 < =3
)
SELECT
*
FROM cteLevels
WHERE [Level] = 3
GO
-- With HierarchyId
SELECT
NodePath.ToString() AS NodeStringPath,
NodeId,
NodePath.GetAncestor(3).ToString() as ParentOn4thPos,
NodeParent,
NodeDepth,
NodeLevel,
NodeDesc
FROM HierarchyTab
WHERE NodeId = 9
GO
Hope you like this and share your experience as comments.
No comments:
Post a Comment
Please Use Good Leanguage