有关SQL Server HierarchyID深度优先性能的问题 [英] Question about SQL Server HierarchyID depth-first performance
问题描述
我正在尝试在包含大约50,000行的表(dbo.[Message])中实现hierarchyID(将来会大幅增长).但是，检索大约25个结果需要30-40秒.
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.
我需要能够深度优先遍历表，并让hierarchyID列(dbo.[Message] .MessageID)成为聚类主键，还添加了一个计算所得的smallint(dbo.[Message] .Hierarchy)存储节点的级别.
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.
用法:.Net应用程序通过一个architectureID值传递到数据库中，我希望能够检索该节点的所有(如果有的话)子代和父代(除根之外，因为它是填充符).
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?
过去的几天我一直在寻找解决此问题的方法，但无济于事. 我将不胜感激，这是我的第一篇文章.如果这被认为是一个讨厌"的问题，我谨此致歉.我已经阅读了MS文档并搜索了无数论坛，但没有对它进行简洁的描述.具体问题.
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.
推荐答案
Found workaround here: http://connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-with-hierarchyid-fun-isdescendantof-in-where-clause#
只是提醒我，我是从应用程序传递的heirarchyID开始的，我的目标是检索该值的任何和所有亲戚(祖先和后代).
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).
在我的特定示例中，我必须在SELECT语句之前添加以下声明:
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))
WHERE子句已更改为:
messageid.IsDescendantOf(@topNode)=1 AND (messageid > @leftNode ) AND (messageid < @rightNode )
查询性能的提高非常显着:
The querying performance increase is very significant:
对于每个传入的结果，搜索时间现在平均为20毫秒(从120到420).
For every result passed in, seek time is now 20ms on average (was from 120 to 420).
查询25个值时，以前需要25-35秒才能返回所有相关节点(在某些情况下，每个值都有很多亲戚，在某些情况下则没有亲戚).现在只需要2秒钟.
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.
这篇关于有关SQL Server HierarchyID深度优先性能的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
