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()似乎仍然较慢.有谁能解释一下？

解决方法

我修改了@Phil Sandler的代码,以消除调用GETDATE()的影响(可能涉及硬件效果/中断??),并使行长度相同.

[自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))