I've been involved in some testing an analysis regarding the use of
GUIDs for PKs and their impact on performance. To quickly stave off the
obvious question - we're doing this because of Replication. The other
thing to note is that we're doing this with SQL 2005.
So the basic test started out as a comparison between using the GUID PK
vs using an Identity (int) PK. The basic setup for this involved
allowing the PK to set its value through the Default - so the Identity
did it's thing of incrementing the counter and the GUID example used a
default of NEWID(). Not surprising, the inserts into the table defined
with an int PK was about 15% faster.
What WAS surprising was that when I changed the test to use a new SQL
2005 function called NEWSEQUENTIALID() [instead of NEWID()] I found that
the GUID version was now nearly 40% *faster* than the int version!! Not
really believing was I was seeing, I re-ran the test several times,
played around with the general makeup of the rest of the columns, etc.
and the results remained fairly consistent. (I actually based part of
the test on code I found on Patrick Wellink's BLOG
(http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx).
Anyway, I wanted to present this to "the group" both for information and
as a sanity check. I'm including the test script below so you can play
with it yourselves (assuming you have SQL 2005). The general idea is
that after clearing out the buffers and cache, it creates a temp table,
inserts 100,000 records, then drops the table. Does this three times
using the different PK options described above. MAYBE my test is
completely invalid (please tell me!!) or, maybe this new feature really
IS that good :)
----
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET NOCOUNT ON
DECLARE @MaxCount Int
-- Set max # of inserts to use for the test.
Set @MaxCount = 100000
DECLARE @START DATETIME
DECLARE @END DATETIME
DECLARE @COUNTER INT
CREATE TABLE TESTTABLEGUID
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
FIELD1 CHAR(200) DEFAULT NEWID(),
FIELD2 CHAR(200) DEFAULT NEWID(),
FIELD3 CHAR(200) DEFAULT NEWID(),
FIELD4 CHAR(200) DEFAULT NEWID(),
FIELD5 CHAR(200) DEFAULT NEWID(),
FIELD6 CHAR(200) DEFAULT NEWID(),
FIELD7 CHAR(200) DEFAULT NEWID(),
FIELD8 CHAR(200) DEFAULT NEWID(),
FIELD9 CHAR(200) DEFAULT NEWID(),
FIELD10 CHAR(200) DEFAULT NEWID(),
FIELD11 CHAR(200) DEFAULT NEWID(),
FIELD12 CHAR(200) DEFAULT NEWID(),
FIELD13 CHAR(200) DEFAULT NEWID(),
FIELD14 CHAR(200) DEFAULT NEWID(),
FIELD15 VARCHAR(200) DEFAULT NEWID()
)
SET @START = GETDATE()
SET @COUNTER = 1
WHILE @COUNTER < @MaxCount
BEGIN
INSERT TESTTABLEGUID DEFAULT VALUES
SET @COUNTER = @COUNTER + 1
END
SET @END = GETDATE()
SELECT 'DURATION GUID',DATEDIFF(MS,@START,@END)
DROP TABLE TESTTABLEGUID
-- IDENTICAL CODE BUT NOW WITH NEWSequentialID()
DECLARE @START2 DATETIME
DECLARE @END2 DATETIME
DECLARE @COUNTER2 INT
CREATE TABLE TESTTABLEGUID
(
ID UNIQUEIDENTIFIER DEFAULT NEWSequentialID() PRIMARY KEY,
FIELD1 CHAR(200) DEFAULT NEWID(),
FIELD2 CHAR(200) DEFAULT NEWID(),
FIELD3 CHAR(200) DEFAULT NEWID(),
FIELD4 CHAR(200) DEFAULT NEWID(),
FIELD5 CHAR(200) DEFAULT NEWID(),
FIELD6 CHAR(200) DEFAULT NEWID(),
FIELD7 CHAR(200) DEFAULT NEWID(),
FIELD8 CHAR(200) DEFAULT NEWID(),
FIELD9 CHAR(200) DEFAULT NEWID(),
FIELD10 CHAR(200) DEFAULT NEWID(),
FIELD11 CHAR(200) DEFAULT NEWID(),
FIELD12 CHAR(200) DEFAULT NEWID(),
FIELD13 CHAR(200) DEFAULT NEWID(),
FIELD14 CHAR(200) DEFAULT NEWID(),
FIELD15 VARCHAR(200) DEFAULT NEWID()
)
SET @START2 = GETDATE()
SET @COUNTER2 = 1
WHILE @COUNTER2 < @MaxCount
BEGIN
INSERT TESTTABLEGUID DEFAULT VALUES
SET @COUNTER2 = @COUNTER2 + 1
END
SET @END2 = GETDATE()
SELECT 'DURATION NEWSequentialID',DATEDIFF(MS,@START2,@END2)
DROP TABLE TESTTABLEGUID
-- IDENTICAL CODE BUT NOW WITH AN IDENTITY PK
DECLARE @START1 DATETIME
DECLARE @END1 DATETIME
CREATE TABLE TESTTABLEID
(
ID INT IDENTITY PRIMARY KEY,
FIELD1 CHAR(200) DEFAULT NEWID(),
FIELD2 CHAR(200) DEFAULT NEWID(),
FIELD3 CHAR(200) DEFAULT NEWID(),
FIELD4 CHAR(200) DEFAULT NEWID(),
FIELD5 CHAR(200) DEFAULT NEWID(),
FIELD6 CHAR(200) DEFAULT NEWID(),
FIELD7 CHAR(200) DEFAULT NEWID(),
FIELD8 CHAR(200) DEFAULT NEWID(),
FIELD9 CHAR(200) DEFAULT NEWID(),
FIELD10 CHAR(200) DEFAULT NEWID(),
FIELD11 CHAR(200) DEFAULT NEWID(),
FIELD12 CHAR(200) DEFAULT NEWID(),
FIELD13 CHAR(200) DEFAULT NEWID(),
FIELD14 CHAR(200) DEFAULT NEWID(),
FIELD15 VARCHAR(200) DEFAULT NEWID()
)
SET @START1 = GETDATE()
SET @COUNTER = 1
WHILE @COUNTER < @MaxCount
BEGIN
INSERT TESTTABLEID DEFAULT VALUES
SET @COUNTER = @COUNTER + 1
END
SET @END1 = GETDATE()
SELECT 'DURATION ID',DATEDIFF(MS,@START1,@END1)
DROP TABLE TESTTABLEID
MS Sql Server LazyDBA home page