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

我正在尝试在包含大约 50,000 行（将来会大幅增长）的表 (dbo.[Message]) 中实现 HierarchyID。然而，检索大约 25 个结果需要 30-40 秒。

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

我需要能够深度优先遍历表，并将 HierarchyID 列 (dbo.[Message].MessageID) 作为集群主键，还添加了一个计算的smallint (dbo.[Message].Hierarchy)，它存储节点的级别。

用法：.Net应用程序将hierarchyID值传递到数据库中，我希望能够检索该节点的所有（如果有）子节点和父节点（除了根节点，因为它是填充符）。

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

@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 文档并搜索了无数论坛，但没有找到简洁的描述具体问题。

目前尚不完全清楚您是要针对深度优先搜索还是广度优先搜索进行优化；问题建议深度优先，但最后的评论是关于广度优先的。

您拥有深度优先所需的所有索引（只需索引hierarchyid柱子）。对于广度优先，仅仅create计算出的level列，您也必须对其进行索引：

ALTER TABLE Message
ADD [Level] AS MessageID.GetLevel()

CREATE INDEX IX_Message_BreadthFirst
ON Message (Level, MessageID)
INCLUDE (...)

（请注意，对于非聚集索引，您很可能需要INCLUDE- 否则，SQL Server 可能会采取聚集索引扫描的方式。）

现在，如果你想找到所有祖先对于一个节点，您需要采取稍微不同的策略。您可以闪电般地进行这些搜索，因为 - 这就是最酷的地方hierarchyid- 每个节点已经“包含”其所有祖先。

我使用 CLR 函数来尽可能快地完成此操作，但您可以使用递归 CTE 来完成此操作：

CREATE FUNCTION dbo.GetAncestors
(
    @h hierarchyid
)
RETURNS TABLE
AS RETURN
WITH Hierarchy_CTE AS
(
    SELECT @h AS id

    UNION ALL

    SELECT h.id.GetAncestor(1)
    FROM Hierarchy_CTE h
    WHERE h.id <> hierarchyid::GetRoot()
)
SELECT id FROM Hierarchy_CTE

现在，要获取所有祖先和后代，请像这样使用它：

DECLARE @MessageID hierarchyID   /* passed in from application */

SELECT m.MessageID, m.MessageComment 
FROM Message as m
WHERE m.MessageId.IsDescendantOf(@MessageID) = 1
OR m.MessageId IN (SELECT id FROM dbo.GetAncestors(@MessageID.GetAncestor(1)))
ORDER BY m.MessageID

尝试一下 - 这应该可以解决您的性能问题。