Why do sequential GUID keys perform faster than sequential INT keys in my test case?

40 votes
6 answers
12256 views
                          After asking this  question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with newsequentialid(), and 2) a table with an INT primary key initialized sequentially with identity(1,1). I would expect the latter to be fastest because of the smaller width of integers, and it also seems simpler to generate a sequential integer than a sequential GUID. But to my surprise, INSERTs on the table with the integer key were significantly slower than the sequential GUID table.

This shows the average time usage (ms) for the test runs: 

    NEWSEQUENTIALID()  1977
    IDENTITY()         2223

Can anyone explain this?

The following experiment was used:

    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,
    SomeDate DATETIME, batchNumber BIGINT, 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,batchNumber) VALUES (GETDATE(),@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,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
    FROM TestInt
    GROUP BY batchNumber
    
    DROP TABLE TestGuid2
    DROP TABLE TestInt

**UPDATE:**
Modifying the script to perform the insertions based on a TEMP table, like in the examples by by Phil Sandler, Mitch Wheat and Martin below, I also find that IDENTITY is faster as it should be. But that is not the conventional way of inserting rows, and I still do not understand why the experiment went wrong at first: 
even if I omit GETDATE() from my original example, IDENTITY() is still way slower. So it seems that the only way to make IDENTITY() outperform NEWSEQUENTIALID() is to prepare the rows to insert in a temporary table and perform the many insertions as a batch-insert using this temp table. All in all, I don't think we have found an explanation to the phenomenon, and IDENTITY() still seems to be slower for most practical usages. Can anyone explain this?
                        
Asked by someName (591 rep)
May 13, 2011, 10:02 PM
Last activity: Jun 23, 2025, 02:49 PM
Why do sequential GUID keys perform faster than sequential INT keys in my test case?

Related Questions
