用Clr实现的sql表值函数splitIDs
在我们需要批量删除数据，或者批量修改实体的状态时，为了性能我们会直接写一个存储过程，并将这一批数据的id用“,”分隔传递给一个存储过程，然后在存储过程中拆分这个字符串，然后执行删除或者更新状态操作。以前每次执行这种操作时我都会在存储过程中拆分字符串。现在sql server支持用.net clr的程序集写函数，存储过程等等。
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);
}
}
USE [DB_Name]
GO
if (object_id('SplitIDs') is not null)
drop function splitIds;
GO
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerUtility')
DROP ASSEMBLY [SqlServerUtility]
go
CREATE ASSEMBLY SqlServerUtility
FROM 'D:\Program Files\Microsoft SQL Server\90\UserDefinedAssembly\SqlServerUtility.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION SplitIDs(@ids Nvarchar(max))
RETURNS TABLE (id bigint)
AS
EXTERNAL NAME SqlServerUtility.SplitIDs.DoSplit
GO
EXEC sp_configure "clr enabled",1
RECONFIGURE
GO
Use DB_Name
GO
if (object_id('splitIds') is not null)
drop function splitIds;
GO
CREATE FUNCTION splitIds(
@strIds varchar(max)
)
RETURNS @IDs table (id bigint)
AS
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
GO
t-sql和clr实现的性能比较
执行的结果是：
splitIDs_tsql执行时间10060
splitIDs_clr执行时间266
现在我们就牛刀小试，做一个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]GOif (object_id('SplitIDs') is not null)drop function splitIds;GOIF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServerUtility')DROP ASSEMBLY [SqlServerUtility]goCREATE ASSEMBLY SqlServerUtility FROM 'D:\Program Files\Microsoft SQL Server\90\UserDefinedAssembly\SqlServerUtility.dll' WITH PERMISSION_SET = SAFEGOCREATE FUNCTION SplitIDs(@ids Nvarchar(max))RETURNS TABLE (id bigint)AS EXTERNAL NAME SqlServerUtility.SplitIDs.DoSplitGOEXEC sp_configure "clr enabled",1 RECONFIGUREGO
注册好了，我们来一个小例子，测试一下函数的执行情况：
select * from splitIDs('1,2,9,1000');
select * from splitIDs('333');
select * from splitIDs(NULL);
执行结果一切正常。
-------------t-sql实现分隔------------
当然这个函数还可以用t-sql直接实现，实现代码如下：
Use DB_NameGOif (object_id('splitIds') is not null)drop function splitIds;GOCREATE FUNCTION splitIds( @strIds varchar(max) )RETURNS @IDs table (id bigint)ASBEGIN 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 ;ENDGO最后测试一下用clr实现和sql直接实现的性能差别；我们声明一个很长的字符串变量，然后让clr的splitIDs和t-sql的splitIDs分别做拆分10次，比较他们的耗时
测试脚本 （在测试脚本中使用的函数名字分别是splitIDs_tsql,splitIDs_clr）
t-sql和clr实现的性能比较
splitIDs_tsql执行时间10060
splitIDs_clr执行时间266
很明显，用clr写的拆分函数要比tsql的拆分函数快大约50倍。
全文结束
