Home Ask Login Register

Developers Planet

Your answer is one click away!

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


Quote of the day: live life