Question about SQL Server HierarchyID depth-first performance
- by AndalusianCat
I am trying to implement hierarchyID in a table (dbo.[Message]) containing roughly 50,000 rows (will grow substantially in the future). However it takes 30-40 seconds to retrieve about 25 results.
The root node is a filler in order to provide uniqueness, therefor every subsequent row is a child of that dummy row.
I need to be able to traverse the table depth-first and have made the hierarchyID column (dbo.[Message].MessageID) the clustering primary key, have also added a computed smallint (dbo.[Message].Hierarchy) which stores the level of the node.
Usage: A .Net application passes through a hierarchyID value into the database and I want to be able to retrieve all (if any) children AND parents of that node (besides the root, as it is filler).
A simplified version of the query I am using:
@MessageID hierarchyID /* passed in from application */
SELECT
m.MessageID, m.MessageComment
FROM
dbo.[Message] as m
WHERE
m.Messageid.IsDescendantOf(@MessageID.GetAncestor((@MessageID.GetLevel()-1))) = 1
ORDER BY
m.MessageID
From what I understand, the index should be detected automatically without a hint.
From searching forums I have seen people utilizing index hints, at least in the case of breadth-first indexes, as apparently CLR calls may be opaque to the query optimizer.
I have spent the past few days trying to find a solution for this issue, but to no avail.
I would greatly appreciate any assistance, and as this is my first post, I apologize in advance if this would be considered a 'noobish' question, I have read the MS documentation and searched countless forums, but have not came across a succinct description of the specific issue.
