用Clr实现的sql表值函数splitIDs

在我们需要批量删除数据，或者批量修改实体的状态时，为了性能我们会直接写一个存储过程，并将这一批数据的id用“,”分隔传递给一个存储过程，然后在存储过程中拆分这个字符串，然后执行删除或者更新状态操作。以前每次执行这种操作时我都会在存储过程中拆分字符串。现在sql server支持用.net clr的程序集写函数，存储过程等等。

现在我们就牛刀小试，做一个clr的sql表值函数。该函数的功能就是传入一个用逗号分隔的数字id字符，返回一个只有一列id的表。
第一步：我们需要新建一个类库项目，并添加一个类SplitIDs

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Data.SqlTypes;

public class SplitIDs

{

[SqlFunction(FillRowMethodName = "FillRow")]

public static IEnumerable DoSplit(String strIDs)

{

return strIDs.Split(',');

}

public static void FillRow(Object obj, out SqlInt64 id)

{

long value = 0;

long.TryParse((string)obj, out value);

id = new SqlInt64(value);

}

有2个注意的点
1. 命名空间声明要去掉，我在测试的过程中刚开始有命名空间的声明，总是注册不成功，后来去掉了存储过程的声明，才注册上
2. 方法必须是静态的并且要有SqlFunction特性，表值函数的返回值是IEnurable

第二步：注册程序集到sql server中

USE [DB_Name]

if (object_id('SplitIDs') is not null)

drop function splitIds;

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerUtility')

DROP ASSEMBLY [SqlServerUtility]

CREATE ASSEMBLY SqlServerUtility

FROM 'D:\Program Files\Microsoft SQL Server\90\UserDefinedAssembly\SqlServerUtility.dll'

WITH PERMISSION_SET = SAFE

CREATE FUNCTION SplitIDs(@ids Nvarchar(max))

RETURNS TABLE (id bigint)

EXTERNAL NAME SqlServerUtility.SplitIDs.DoSplit

EXEC sp_configure "clr enabled",1

RECONFIGURE

注册过程中需要注意的是上面注册sql中的最后一块，配置在数据库中启用clr enabled属性。否则即使注册上了函数也不能执行。
注册好了，我们来一个小例子，测试一下函数的执行情况：
select * from splitIDs('1,2,9,1000');
select * from splitIDs('333');
select * from splitIDs(NULL);
执行结果一切正常。
-------------t-sql实现分隔------------
当然这个函数还可以用t-sql直接实现，实现代码如下：

Use DB_Name

if (object_id('splitIds') is not null)

drop function splitIds;

CREATE FUNCTION splitIds(

@strIds varchar(max)

)

RETURNS @IDs table (id bigint)

BEGIN

declare @i int,@j int,@l int,@v bigint;

SET @i = 0;

SET @j = 0;

SET @l = len(@strIds);

while (@j < @l)

BEGIN

SET @j = charindex(',',@strIds,@i+1);

IF @j = 0 set @j = @l+1;

SET @v = cast(substring(@strIds,@i+1,@j-@i-1) as bigint);

INSERT INTO @IDs(id)VALUES(@v)

SET @i = @j;

END

RETURN ;

END

最后测试一下用clr实现和sql直接实现的性能差别；我们声明一个很长的字符串变量，然后让clr的splitIDs和t-sql的splitIDs分别做拆分10次，比较他们的耗时

测试脚本（在测试脚本中使用的函数名字分别是splitIDs_tsql,splitIDs_clr）

t-sql和clr实现的性能比较

执行的结果是：
splitIDs_tsql执行时间10060
splitIDs_clr执行时间266

很明显，用clr写的拆分函数要比tsql的拆分函数快大约50倍。

全文结束

Tag标签: t-sql,clr,function

posted on 2008-04-28 17:08 玉开阅读(1510) 评论(12) 编辑收藏所属分类: 数据库、sql server

与我联系

常用链接

留言簿(12)

我管理的小组

我参加的小组

我参与的团队

我的标签

随笔分类

随笔档案

文章分类

相册

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜

60天内阅读排行