解析SQL中树形分层数据的查询优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了解析SQL中树形分层数据的查询优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3543字，纯文字阅读大概需要6分钟。

内容图文

在数据查询中，从2008开始SQL Server提供了一个新的数据类型hierarchyid，专门用来操作层次型数据结构。

hierarchyid 类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是：对从树的根目录到该节点的路径进行编码。

这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。 表示形式以一条斜杠开头，只访问根的路径由单条斜杠表示。 对于根以下的各级，各标签编码为由点分隔的整数序列。 子级之间的比较就是按字典顺序比较由点分隔的整数序列。 每个级别后面紧跟着一个斜杠。 因此斜杠将父级与其子级分隔开。 例如，以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的有效 hierarchyid 路径：

? /

? /1/

? /0.3.-7/

? /1/3/

? /0.1/0.2/

在没有hierarchyid的日子里，我们通过CTE的方式来查询父以及全部的下级，但是，数据量多的情况下，CTE的方式将会变的很慢，后来，我们通过构造PATH的方式来加快速度。那么，有了hierarchyid类型后，自然得使用hierarchyid了。

现在，通过一个实际的例子来看看hierarchyid的威力。

一：CTE方式

WITH CTEGetChild AS  
(  
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2
    UNION ALL  
     (
        SELECT A.* FROM EL_Organization.Organization AS A
        INNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID  --and A.[State]=0 and A.AuditState=2
     )  
)

查询出来4489行，需要25S。

看来CTE方式已经到了不能容忍的地步，那么，现在，我们就用它来进行优化。

二：hierarchyid

首先，我们得新建该字段，然后为其赋值，

create function f_cidname(@id varchar(50)) returns varchar(max) as 
begin 
declare @pids nvarchar(max); 
declare @pNames nvarchar(max); 
set @pids=''; 
set @pNames=''; 
with cte as 
( select id,parentid,name from EL_Organization.Organization where id =@id--'00037fdf184e48d084b87c3499e3c0e5'
union all 
select b.id,b.parentid,b.name from cte A ,EL_Organization.Organization B where a.parentid = b.id 
)
select @pids=convert(varchar(32),Convert(int, Convert(varbinary(max), id)))  + '/'+ @pids from cte 
return [email protected]
end 
go

接着，我们需要Update全表：

UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)

注意，id是guid的32位字符串，而hierarchyid字段不支持那么大的Path内路径，于是我们将GUID转为了整型：convert(varchar(32),Convert(int, Convert(varbinary(max), id)))

2.1 TIP

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

注意，极有可能我们把字段更新上去后，我们的程序却出错了，如上。这个时候，我们需要把

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

这个DLL打包到我们的应用程序中去。原因不解释了。

看看效果吧，修改过后的代码为：

DECLARE @tmpIds hierarchyid
SELECT @tmpIds=Pids FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4';
WITH CTEGetChild AS  (
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4'
    UNION ALL(
    SELECT * FROM EL_Organization.Organization WHERE Pids.IsDescendantOf(@tmpIds)=1 
    )
)
SELECT * FROM CTEGetChild

现在，我们的时间到了1S内。

2.2 一切为了不动应用层代码

现在，既然，增加了一个字段，我们就要维护这个字段，如：本条记录在应用程序中被移动到了别的父级下，就需要更新这个字段。为了不动上层代码，唯一能做的就是创建触发器，即：原有的ParentId变动的时候，就需要更新这个PIds字段，于是，我们创建触发器如下：

create trigger UpdateOrgPIds
on EL_Organization.Organization
after update
as
if update ([ParentId])
begin
     declare @tmpId varchar(36)
     select @tmpId=id from inserted 
     update EL_Organization.Organization set pids=dbo.f_cidname(@tmpId)
end 
go
-- drop  trigger EL_Organization.UpdateOrgPIds

以上就是解析SQL中树形分层数据的查询优化的详细内容，更多请关注Gxl网其它相关文章！

内容总结

以上是互联网集市为您收集整理的解析SQL中树形分层数据的查询优化全部内容，希望文章能够帮你解决解析SQL中树形分层数据的查询优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端