Riccardo February 2016

SQL Server 2012 IsDescendantOf SLOW

Let's say that the table 'Items' (about 1000 records) has the following fields:

ItemID (smallint, primary key)
ItemHierarchyID (HierarchyID)
ItemName (varchar(max))

This query (just for testing purposes) takes about 0 seconds:

    SELECT A.*
    FROM Items A, Items B
    WHERE A.ItemID = B.ItemID

    (output: 1011 records)

Instead this other takes about 25 seconds:

     SELECT A.*
     FROM Items A, Items B
     WHERE A.ItemHierarchyID.IsDescendantOf(B.ItemHierarchyID) = 1

     (output: 1035 records)

I add that many of these records have ItemHierarchyID = NULL.

Why IsDescendantOf is so slow?

Answers


Jaco February 2016

A breadth first index could improve your query performance. Change your table definition to this:

ItemID (smallint, primary key)
ItemHierarchyID (HierarchyID)
HierarchyLevel as ItemHierarchyID.GetLevel()
ItemName (varchar(max))

and then add the following index:

CREATE CLUSTERED INDEX Items_Breadth_First ON Items(HierarchyLevel,ItemHierarchyID ) ;


Riccardo February 2016

I had forgotten that Item ItemHierarchyID is a computed column. That was the problem. Sorry.

Post Status

Asked in February 2016
Viewed 2,595 times
Voted 7
Answered 2 times

Search




Leave an answer