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

在数据查询中，从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 '/'+@pids
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

微信扫一扫，关注最课程，获取每日一练