tsql 和 clr 的性能实测比对(转)

这两天一直被一个问题困扰, 就是到底是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