有关SQL Server HierarchyID深度优先性能的问题

Question

我正在尝试在包含大约50,000行的表（dbo。[Message]）中实现hierarchyID（将来会大大增加）。但是，检索大约25个结果需要30-40秒。

根节点是填充符，以提供唯一性，因此，随后的每一行都是该哑行的子级。

我需要能够深度优先遍历表，并让hierarchyID列（dbo。[Message]
.MessageID）成为聚类主键，还添加了计算所得的smallint（dbo。[Message] .Hierarchy），用于存储节点的级别。

用法：一个.Net应用程序通过一个architectureID值传递到数据库中，我希望能够检索该节点的所有（如果有的话）子代和父代（除根之外，因为它是填充符）。

我正在使用的查询的简化版本：

@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

据我了解，该索引应自动检测而无需任何提示。

从搜索论坛中，我看到人们在处理广度优先的索引时会利用索引提示，但没有在深度优先的情况下观察到此应用程序。这是与我的方案相关的方法吗？

在过去的几天中，我一直在寻找解决此问题的方法，但无济于事。我将不胜感激，这是我的第一篇文章。如果这被认为是一个“讨厌”的问题，我事先表示歉意，我已经阅读了MS文档并搜索了无数论坛，但没有看到对它的简洁描述。具体问题。

Answer 1

在这里找到解决方法：http :
//connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-
with-hierarchyid-fun-isdescendantof-in-where-
clause#

只是提醒我，我heirarchyID是从应用程序传递过来的，我的目标是检索该值的任何和所有亲戚（祖先和后代）。

在我的特定示例中，我必须在SELECT语句之前添加以下声明：

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 )

查询性能的提高非常重要：

对于每个传入的结果，查找时间现在平均为20毫秒（从120到420）。

查询25个值时，以前需要25-35秒才能返回所有相关节点（在某些情况下，每个值都有很多亲戚，在某些情况下则没有亲戚）。现在仅需2秒钟。

非常感谢所有在此站点和其他站点上对此问题做出贡献的人。