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?