tsql - SQL Server 2012 IsDescendantOf SLOW -

let's table 'items' (about 1000 records) has following fields:

itemid (smallint, primary key) itemhierarchyid (hierarchyid) itemname (varchar(max))

this query (just testing purposes) takes 0 seconds:

    select a.*     items a, items b     a.itemid = b.itemid      (output: 1011 records)

instead other takes 25 seconds:

     select a.*      items a, items b      a.itemhierarchyid.isdescendantof(b.itemhierarchyid) = 1       (output: 1035 records)

i add many of these records have itemhierarchyid = null.

why isdescendantof slow?

a breadth first index improve query performance. change table definition this:

itemid (smallint, primary key) itemhierarchyid (hierarchyid) hierarchylevel itemhierarchyid.getlevel() itemname (varchar(max))

and add following index:

create clustered index items_breadth_first on items(hierarchylevel,itemhierarchyid ) ;

Color