Get Downline and Upline of hierarchical data and Performance review - CTE vs HierarchyId in SQL Server 2008
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) ;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 GOWhen you run above script you have data and execution plans as follow,
2. Get up line data using CTE and HierarchyId and compare the execution plan.
-- Using CTE (Not Using NodePath and NodeLevel) ;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 GOThe result set and execution plan when you run above script
Hope you like this and share your experience as comments.
Comments
Post a Comment