SQLSERVER CLR無序自增(支援並發)效能對比

CLR函數指令碼

---------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

/// <summary>
/// 使用者自定CLR 函數，用來產生一個序列
/// </summary>

public partial class test
{
    
    public static Hashtable ht=new Hashtable(); //建立一個Hashtable執行個體
 
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt64 GetRowNum(SqlGuid Guid1)
    {
        try
        {
            if (!ht.Contains(Guid1)) //判斷雜湊表是否包含特定鍵,其傳回值為true或false
            {
                ht[Guid1] = 0;
            }
            Int64 i = Convert.ToInt64(ht[Guid1].ToString());
            i++;
            ht[Guid1] = i.ToString();
            return i;

        }
        catch
        {
            return -1;
        }
    }

    /// <summary>
    /// 刪除雜湊表值
    /// </summary>
    /// <param name="Guid1"></param>
    [Microsoft.SqlServer.Server.SqlProcedure ]
    public static void ClearGuid(SqlGuid Guid1)
    {
        try
        {                
            ht.Remove(Guid1);
            return ;
        }
        catch
        {

        }
    }
};

 

------------------------------------------------------------------------------------------------------------------------------

啟動CLR

exec sp_configure 'show advanced options','1';
go
reconfigure  WITH OVERRIDE
go
exec sp_configure 'clr enabled','1'
go
reconfigure WITH OVERRIDE 

ALTER DATABASE   test SET TRUSTWORTHY On

-----------------------------------------------------------------------
drop FUNCTION  GetRowNum
drop proc ClearGuid

drop ASSEMBLY testss

CREATE ASSEMBLY testss FROM 'C:\Documents and Settings\Administrator\案頭\test\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll' WITH PERMISSION_SET = UnSAFE;

 

go

建立函數
CREATE FUNCTION dbo.GetRowNum
(
 @Guid uniqueidentifier
)
RETURNS bigint
AS EXTERNAL NAME
  testss.test.GetRowNum

 

CREATE proc ClearGuid
(
  @Guid uniqueidentifier
)
AS EXTERNAL NAME
  testss.test.ClearGuid

 

 ----------------------------------------------------------------------------------------------------

 

GetRowNum與identity對比

 

declare @Guid uniqueidentifier
set @Guid=newid()

select dbo.GetRowNum(@Guid)
  from list

 

--清理雜湊表

exec ClearGuid  @Guid

 

select identity(int ,1,1) as id into #t  from list
 
select id  from #t
drop table #t

資料量943761

 

時間匆忙，經多次測試，初步結果

1.GetRowNum(5591)消耗時間比identity(6278)少近700毫秒

2.GetRowNum(2171)的CPU比identity（922）高很多

3.GetRowNum（2352）讀是identity（4828）的一半

4.GetRowNum沒有寫入,identity的寫入是1519

 

初步結論

1.GetRowNum消耗更多的CPU，進行運算

2.GetRowNum的磁碟消耗比identity低，而且是沒有寫入的

3.如果表已經有自增(不連續),identity就不能用，GetRowNum可以

4.GetRowNum瞬間返回資料，identity要停頓

 

......................