这两天一直被一个问题困扰, 就是到底是tsql的性能好, 还是 CLR user-defined function 的性能好. MSDN上是这么说的, Microsoft对 CLR和sql 的集成做了很多优化, 所以, CLR user-defined function 的性能要好于 常规的 user defined function, 而且是好不少. 不过也有人说, 还是应当尽量使用TSQL. 在网上找了找资料, 仍然是迷惑不解. 所以, 决定针对性的做个测试. 这个测试的结果是, tsql的性能, 起码比clr好4倍. 以下是测试代码.
//
//************* C# clr user defined type:
using System;
using System.Data;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace MyTestNameSpace
{
[Serializable]
[StructLayout(LayoutKind.Sequential)]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native, IsByteOrdered = true, ValidationMethodName = "Validate")]
public class Test1 : INullable
{
public Test1()
{
m_Null = false;
}
public Test1(int city, int level, int age1, int age2, bool extra)
{
City = city;
Level = level;
Age1 = age1;
Age2 = age2;
Extra = extra;
m_Null = false;
}
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static Test1 NewTest1(int city, int level, int age1, int age2, bool extra)
{
return new Test1(city, level, age1, age2, extra);
}
[SqlMethod(DataAccess = DataAccessKind.None, InvokeIfReceiverIsNull = false, IsPrecise = true, IsDeterministic = true, OnNullCall = false)]
public bool Check(int city, int level, int age, bool extra)
{
if (city == this.City &&
level > this.Level &&
age >= this.Age1 &&
age <= this.Age2 &&
extra == this.Extra)
return true;
else
return false;
}
public override string ToString()
{
// Replace the following code with your code
return "";
}
public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}
public static Test1 Null
{
get
{
Test1 h = new Test1();
h.m_Null = true;
return h;
}
}
public static Test1 Parse(SqlString s)
{
return Null;
}
public bool Validate()
{
return true;
}
public int Age1;
public int Age2;
public int Level;
public int City;
public bool Extra;
private bool m_Null;
}
}
//************* C# clr user defined type end
创建数据库对象
--****** --Creating the assembly
-- note: replace with your own path and/or name
CREATE ASSEMBLY [MyTestAsm]
FROM 'MyTestAsm.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE TYPE [dbo].[Test1]
EXTERNAL NAME [MyTestAsm].[MyTestNameSpace.Test1]
go
Create FUNCTION [dbo].[NewTest1](@city [int], @level [int], @age1 [int], @age2 [int], @extra [bit])
RETURNS [dbo].[Test1] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyTestAsm].[MyTestNameSpace.Test1].[NewTest1]
go
--****** create table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[OID] [int] IDENTITY(1,1) NOT NULL,
[City] [int] NOT NULL,
[Age] [int] NOT NULL,
[Level] [int] NOT NULL,
[Extra] [int] NOT NULL,
[SCity] [int] NOT NULL,
[SAge1] [int] NOT NULL,
[SAge2] [int] NOT NULL,
[SLevel] [int] NOT NULL,
[SExtra] [int] NOT NULL,
[Data] [dbo].[Test1] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[TestClr]
@oid int
AS
BEGIN
declare @scity int, @sage1 int, @sage2 int, @slevel int, @sextra bit
declare @data dbo.Test1
SET NOCOUNT ON;
select @data = data from dbo.test where oid = @oid
select oid, age, city, extra, level from dbo.test
where @data.[Check](city, level, age, extra) = 1
END
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Testsproc]
@oid int
AS
BEGIN
declare @scity int, @sage1 int, @sage2 int, @slevel int, @sextra bit
SET NOCOUNT ON;
select @scity = scity, @sage1 = sage1, @sage2 = sage2, @slevel = slevel,
@sextra = sextra from dbo.test where oid = @oid
select oid, age, city, extra, level from dbo.test
where level > @slevel and city = @scity
and extra = @sextra and age >= @sage1 and age <= @sage2
END
go
产生测试数据
--****** populate with test data
declare @count int
declare @range int
declare @sage1 int, @sage2 int, @slevel int, @scity int, @sextra bit
set @count = 10000
set @range = 10
begin tran -- importanct without this, it takes much longer to ron
while @count > 0
begin
set @sage1 = rand() *@range
set @sage2 = @sage1 + rand() *@range
set @slevel = rand() *@range
set @scity = rand() *@range
set @sextra = case when rand()>0.5 then 1 else 0 end
INSERT INTO Test
(Age, City, Extra, Level, SAge1, Sage2, SLevel, SCity, SExtra, Data)
VALUES (rand()*@range, rand()*@range, case when rand()>0.5 then 1 else 0 end , rand() * @range,
@SAge1,@Sage2,@SLevel,@SCity,@SExtra, dbo.NewTest1(@SCity,@SLevel, @SAge1,@Sage2,@SExtra ))
set @count = @count -1
end
commit
go
TSQL 性能测试
--****** performance test for tsql
declare @count int
declare @oid int
declare @inc int
declare @date1 datetime, @date2 datetime
set @count = 40
set @inc = 33
set @date1= getdate()
select @oid = min(oid) from dbo.test
while @count > 0
begin
set @oid = @oid + @inc
exec dbo.Testsproc @oid
set @count = @count -1
end
set @date2 = getdate()
print datediff(ms, @date1, @date2)
go
CLR 性能测试
--****** performance test for clr
declare @count int
declare @oid int
declare @inc int
declare @date1 datetime, @date2 datetime
set @count = 40
set @inc = 33
set @date1= getdate()
select @oid = min(oid) from dbo.test
while @count > 0
begin
set @oid = @oid + @inc
exec dbo.TestClr @oid
set @count = @count -1
end
set @date2 = getdate()
print datediff(ms, @date1, @date2)
go
