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?