Saturday, 6 June 2015

Get Downline and Upline of hierarchical data and Performance review - CTE vs HierarchyId in SQL Server 2008 by serverku

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.
-- 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
GO
When 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
GO
The result set and execution plan when you run above script



Hope you like this and share your experience as comments.

No comments:

Post a Comment

Please Use Good Leanguage