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

Question

我正在尝试在包含大约 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 文档并搜索了无数论坛，但没有遇到过简洁的描述具体问题。

Answer 1

您是否正在尝试针对深度优先搜索或广度优先搜索进行优化尚不完全清楚；这个问题建议深度优先，但最后的评论是关于广度优先的。

您拥有深度优先所需的所有索引(只需索引 hierarchyid 列)。对于广度优先，仅仅创建计算 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

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