GetAncestor query on SQL hierarchyid to always pull certain levels

Question

We commonly get asked to include certain levels of a hierarchy in a report and I am looking for a way to speed up the query performance with hierarchyid. I did some testing and and I have queries that work but I think the performance could be better. Below is a sample that pulls levels 2 through 4 for a given entry. I figured out a way to do this with GetAncestor() function combined with the level of the hierarchyid. The first query seems fast but it is hard coded to only return rows for a certain level to avoid blowing up the GetAncestor query with a negative value. The second sample solves that issue but it is much slower. Idealy the second option is what I'd like to use but it isn't fast enough.

--drop table #hier

CREATE TABLE #hier
    (
    rec_ID int NOT NULL,
    rec_NAME varchar(6),
    nodeID hierarchyid NULL,
    lvl  AS [nodeid].[GetLevel]() PERSISTED 
    )  ON [PRIMARY]
GO
ALTER TABLE #hier ADD CONSTRAINT
    rec_ID PRIMARY KEY CLUSTERED 
    (
    rec_ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_hier_nodeID ON #hier
    (
    nodeID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE #hier SET (LOCK_ESCALATION = TABLE)
GO

insert into #hier (rec_ID, rec_NAME, nodeID)
    SELECT 1, 'CEO', cast('/' as hierarchyid)
    union
    SELECT 2, 'VP1', cast('/1/' as hierarchyid)
    union
    SELECT 3, 'VP2', cast('/2/' as hierarchyid)
    union
    SELECT 4, 'VP3', cast('/3/' as hierarchyid)
    union
    SELECT 5, 'Mgr1', cast('/1/1/' as hierarchyid)
    union
    SELECT 6, 'Mgr2', cast('/1/2/' as hierarchyid)
    union
    SELECT 7, 'Super1', cast('/1/2/1/' as hierarchyid)
    union
    SELECT 8, 'Ldr1', cast('/1/2/1/1/' as hierarchyid)
    union
    SELECT 9, 'Work1', cast('/1/2/1/1/1/' as hierarchyid)
    union
    SELECT 10, 'Work2', cast('/1/2/1/1/2/' as hierarchyid)
    union
    SELECT 11, 'Work3', cast('/1/2/1/1/3/' as hierarchyid)
GO

-- this runs fast but is hard coded to a level

declare @recname varchar(6)
set @recname = 'Work3'

select 
    x.rec_name
    ,x.lvl
    ,(select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 2) ) as l2
    ,(select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 3) ) as l3
    ,(select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 4) ) as l4
from #hier x 
where x.rec_name = @recname
    and x.lvl >= 4


-- this works for all levels but runs too slow

set @recname = 'Mgr2'
select 
    x.rec_name
    ,x.lvl
    ,case
        when x.lvl >=2 then (select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 2) )
        else '*N/A' end as l2
    ,case
        when x.lvl >=3 then (select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 3) )
        else '*N/A' end as l2
    ,case
        when x.lvl >=4 then (select rec_name from  #hier where nodeid = x.nodeid.GetAncestor(x.lvl - 4) )
        else '*N/A' end as l2
from #hier x 
where x.rec_name = @recname

score 0 · Answer 1 · answered Oct 07 '14 at 19:59

In my opinion, you should create following indexes:

1)

CREATE INDEX IX_hier_rec_name_#_lvl_nodeid 
ON #hier (rec_name) 
INCLUDE (lvl, nodeid)

which satisfies query #2

select 
    x.rec_name
    ,x.lvl
    ,case
        when x.lvl >=2 then (... = x.nodeid.GetAncestor(x.lvl - 2) )
        else '*N/A' end as l2
    ,...
from #hier x 
where x.rec_name = @recname

Note: for query #1 you could use

CREATE INDEX IX_hier_rec_name_lvl_#_nodeid 
ON #hier (rec_name, lvl) 
INCLUDE (nodeid)

and

2) you should change this index IX_hier_nodeID with

CREATE INDEX IX_hier_nodeID_#_rec_name 
ON #hier (nodeid) 
INCLUDE (rec_name)

or (better) with

CREATE UNIQUE INDEX IUN_hier_nodeID_#_rec_name 
ON #hier (nodeid) 
INCLUDE (rec_name)

if nodeid values doesn't allow duplicates.

That is a good point, I should have looked deeper into indexes first. I also should have created an example with a loop and more rows to better test performance. Initially I thought maybe there was a way to re-work the query but maybe it is just a simple issue of indexing. In the example rec_ID is unique for sure and nodeID should be unique (assuming my maintenance job works correctly). Let me research your use of the INCLUDE option - I'm not familar with that. — pretzelb, Oct 08 '14 at 13:42

GetAncestor query on SQL hierarchyid to always pull certain levels

1 Answers1