Imagine this table, filled with more than a million records containing a 7-levels tree:
CREATE TABLE HierTest ( Id BIGINT PRIMARY KEY IDENTITY(1,1), Path HIERARCHYID NOT NULL UNIQUE, Name NVARCHAR(100) NOT NULL )
Now imagine there’s a node with Id=1839861 and you want to get all of its ancestors. I know of two ways to do this (sources: Simon Ince’s blog post and StackOverflow):
DECLARE @Hiernode HIERARCHYID; SELECT @Hiernode=[ht].[Path] FROM [TestSchema].[HierTest] AS [ht] WHERE [ht].[Id]=1839861; SELECT *--, --[ht].[Path].ToString() AS PathString FROM [TestSchema].[HierTest] AS [ht] WHERE @Hiernode.[IsDescendantOf]([ht].[Path])=1 ORDER BY [ht].[Path];
and
WITH Tree(Id, Name, Path, AncestorPath) AS( SELECT [ht].[Id], [ht].[Name], [ht].[Path], [ht].[Path].GetAncestor(1) AS 'AncestorPath' FROM [TestSchema].[HierTest] AS [ht] WHERE [ht].[Id]=1839861 UNION ALL SELECT [ht].[Id], [ht].[Name], [ht].[Path], [ht].[Path].GetAncestor(1) AS 'AncestorPath' FROM [TestSchema].[HierTest] AS [ht] INNER JOIN [Tree] t ON [ht].[Path] = [t].[AncestorPath] ) SELECT [t].[Id], [t].[Name], [t].[Path] FROM [Tree] t ORDER BY [t].[Path]
Measuring the execution time shows the “Take II” to be more than 100 times faster than “Take I”!!
I’ll update this post with what I’ll learn in the future.
Download the scripts from: Hierarchy Test.
0 Comments.