我试图在表(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
