• 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

  • 相关阅读:
    ACCP7.0-S2-复习自测-15测试分析
    线程
    多线程下的单例模式
    combobox 属性、事件、方法
    java的多线程总结
    爬虫--登录网页
    shell--字符串是否为空
    python--正则表达式 字符串匹配
    mysql---表所在数据库
    python--日期操作
  • 原文地址:https://www.cnblogs.com/kakrat/p/1323759.html
Copyright © 2020-2023  润新知