Question about SQL Server HierarchyID depth-first performance
hierarchy, hierarchyid, sql, sql-server-2008, t-sql
Solution
Found workaround here: http://connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-with-hierarchyid-fun-isdescendantof-in-where-clause#
Just reminding that I started with a `heirarchyID` passed in from the application and my goal is to retrieve any and all relatives of that value (both Ancestors and Descendants).
In my specific example, I had to add the following declarations before the `SELECT` statement:
declare @topNode hierarchyid = (select @messageID.GetAncestor((@messageID.GetLevel()-1)))
declare @topNodeParent hierarchyid = (select @topNode.GetAncestor(1))
declare @leftNode hierarchyid= (select @topNodeParent.GetDescendant (null, @topNode))
declare @rightNode hierarchyid= (select @topNodeParent.GetDescendant (@topNode, null))
The `WHERE` clause has been changed to:
messageid.IsDescendantOf(@topNode)=1 AND (messageid > @leftNode ) AND (messageid < @rightNode )
The querying performance increase is very significant:
For every result passed in, seek time is now 20ms on average (was from 120 to 420).
When querying 25 values, it previously took 25 - 35 seconds to return all related nodes (in some cases each value had many relatives, in some there were none). It now takes only 2 seconds.
Thank you very much to all who have contributed to this issue on this site and on others.
Problem
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 when dealing with breadth-first indexes, but have not observed this application in depth-first situations. Would that be a relevant approach for my scenario? 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.