SQL2005CLR函数扩展-树的结构

淡淡的烟草味﹌ 2022-04-10 08:20 134阅读 0赞

树结构广泛用在各类分级管理设计中。但他的展现方式也是很让人头疼的事情。比如展开一个靠id和parentid建立关系的分级树，SQL2005已经可以用CTE来递归查询。我们看如下测试代码：  
SQL codeset nocount on  
print '--SQL2005 CTE 树结构测试  
'  
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))

insert into @t values('a',null,'000')  
insert into @t values('b','a','111')  
insert into @t values('c','b','222')  
insert into @t values('d','b','333')  
insert into @t values('f','c','444')  
insert into @t values('e','c','555')  
;with t (id, name, pid, path)  
as(  
select a.id, a.name, a.pid, cast(a.id as varchar(20)) as path  
from @t as a  
where pid is null  
union all  
select a.id, a.name, a.pid, cast(path +'>'+a.id as varchar(20))  
from @t a  
join t as b  
on a.pid = b.id  
)  
select \* from t

/\*  
\--SQL2005 CTE 树结构测试  
id name pid path  
\---------- ---------- ---------- --------------------  
a 000 NULL a  
b 111 a a>b  
c 222 b a>b>c  
d 333 b a>b>d  
f 444 c a>b>c>f  
e 555 c a>b>c>e  
\*/  
set nocount off

当你还不知道这个写法的时候可能会有点激动，但是别忘了他是递归，性能并不比以前的循环+表变量好。我用他展开一个26000条数据，最深5级的权限组分级表时，10分钟后仍然没有查询结束。而我尝试用CLR写扩展函数来实现同样的效果，展开同样的结构，只需要不到2秒.

clr的实现和部署代码这里略去，可以从我的blog原文获取  
[http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx][http_blog.csdn.net_jinjazz_archive_2009_04_28_4133838.aspx]

....

这个CLR包括4个函数，两个负责维护并发key，一个负责建立链表，一个负责返回任意节点的路径。测试sql语句如下

SQL codeset nocount on  
print '--SQL2005 CLR 树结构测试  
'  
declare @t table(id varchar(10) ,pid varchar(10), name varchar(10))

declare @key varchar(40) , @b bit  
set @key=newid()  
select @b=dbo.xfn\_inittreekey(@key)  
select @b=dbo.xfn\_treebuilder(@key,id,pid) from @t  
select \*,cast(dbo.xfn\_gettreepath(@key,id)as varchar(20)) as path from @t  
select @b=dbo.xfn\_disposetreekey(@key)  
go  
/\*  
\--SQL2005 CLR 树结构测试  
id pid name path  
\---------- ---------- ---------- --------------------  
a NULL 000 a  
b a 111 a>b  
c b 222 a>b>c  
d b 333 a>b>d  
f c 444 a>b>c>f  
e c 555 a>b>c>e  
\*/  
set nocount off

这个CLR函数的速度快是因为他牺牲了一部分空间节省了时间，只需要两次全表扫描，时间复杂度应该是O(n)级别的，所以比递归快了很多。目前我还没有发现可以不用提前建立树节点链表集合的方法，所以除了需要调用维护key的两个函数外，还需要调用xfn\_treebuilder和xfn\_gettreepath这两个clr函数。

[http_blog.csdn.net_jinjazz_archive_2009_04_28_4133838.aspx]: http://blog.csdn.net/jinjazz/archive/2009/04/28/4133838.aspx