1

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?

Rick
  • 1,042
  • 2
  • 14
  • 34

2 Answers2

3

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 ) ;
Alex
  • 21,273
  • 10
  • 61
  • 73
1

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

Rick
  • 1,042
  • 2
  • 14
  • 34