sql-server – 为什么在我的测试用例中,顺序GUID键的执行速度比

在扣问 this题目较量次序和非次序GUID之后,我实行较量INSERT机能：1)一个表与GUID主键次序初始化newsequentialid(),和2)一个表,INT主键按次序初始化(1,1).我但愿后者最快,由于整数宽度较小,天生次序整数好像比次序GUID更简朴.但令我惊奇的是,带有整数键的表上的INSERT明明慢于次序GUID表.

这表现了测试运行的均匀时刻行使(ms)：

NEWSEQUENTIALID()  1977
IDENTITY()         2223

有谁能表明一下？

行使以下尝试：

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,SomeDate DATETIME,batchNumber BIGINT,FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000


WHILE (@BatchCounter <= 20)
BEGIN 
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT 
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

更新：
修改剧本以执行基于TEMP表的插入,如下面的Phil Sandler,Mitch Wheat和Martin的示例,我也发明IDENTITY应该更快.但这不是传统的插入行的方法,我如故不大白为什么尝试最初会堕落：
纵然我从原始示例中省略了GETDATE(),IDENTITY()如故会慢一些.因此,好像使IDENTITY()的机能优于NEWSEQUENTIALID()的独一要领是筹备要插入姑且表的行,并行使此姑且表执行多次插入作为批量插入.总而言之,我以为我们没有找到对这种征象的表明,对付大大都现适用法而言,IDENTITY()好像如故较慢.有谁能表明一下？

办理要领

[自SQL Server 2000以来,有几篇文章涉实时序题目和高判别率计时器,以是我想只管镌汰这种影响.]

在简朴的规复模子中,数据和日记文件的巨细都高出了所需的巨细,这里是时刻(以秒为单元):(按照下面简直切代码更新了新功效)

Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060    
       2.570        4.116    
       2.513        3.786   
       2.517        4.173    
       2.410        3.610    
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

行使的代码：

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,rowNum int,adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum,adate) VALUES (@LocalCounter,GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate,batchNumber) 
SELECT adate,rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate,rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms,@IdTimeStart,@IdTimeEnd) AS IdTime,@GUIDTimeStart,@GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

在阅读@ Martin的观测后,我在两种环境下都从头行使了提议的TOP(@num),即

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647; 
DECLARE @GUIDTimeStart DATETIME = GETDATE(); 
INSERT INTO TestGuid2 (SomeDate,batchNumber) 
SELECT TOP(@num) adate,rowNum FROM #temp; 
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate,rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

以下是时刻功效：

Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

我无法得到现实的执行打算,由于查询永久不会返回！这好像是一个bug. (运行Microsoft SQL Server 2008 R2(RTM) – 10.50.1600.1(X64))

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!