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

我试图在表(dbo.[Message])中实现hierarchyID,其中包含大约50,000行(将来会大幅增长).但是,检索大约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

根据我的理解,应该在没有提示的情况下自动检测索引.

从搜索论坛我看到人们在处理广度优先索引时使用索引提示,但是在深度优先的情况下没有观察到这个应用程序.这对我的情景来说是一种相关的方法吗？

过去几天我一直试图找到解决这个问题的方法,但无济于事.
我非常感谢任何帮助,因为这是我的第一篇文章,如果这被认为是一个“noobish”问题,我会事先道歉,我已经阅读了MS文档并搜索了无数论坛,但没有看到简洁的描述具体问题.

解决方法

您是否正在尝试优化深度优先或广度优先搜索并不完全清楚;问题表明深度优先,但最后的评论是关于广度优先的.

您拥有深度优先所需的所有索引(只需索引hierarchyid列).对于广度优先,仅仅创建计算级别列是不够的,您还必须将其编入索引：

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

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

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

解决方法

猜你在找的MsSQL相关文章