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

我想在一个表来实现HIERARCHYID(DBO。[信息])含有大约5万份行(将在未来大幅增长)。 然而,它需要30-40秒,以获取约25的结果。

根节点是为了提供独特性,为此每个后续行就是假行的孩子填料。

我需要能够遍历表深度优先,并取得了HIERARCHYID列(DBO。[信息] .MessageID)集群的主键,还增加了一个计算SMALLINT(DBO。[信息] .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列)。 对于广度优先,这是不够的只是创建计算 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

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

这里找到解决方法: 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秒。

非常感谢所有谁已经在本网站和其他促成了这一问题。

分类:SQL 时间:2015-03-15 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.601 (s). 10 q(s)