0

I'm developing a database-intensive application which maintains about 5 tables. These tables contain many thousands of records each. All the tables use GUID clustered primary keys. To make it efficient, I've dropped foreign-keys between the tables.

I am running a script 65000 lines long which creates a whole bunch of tables (including my tables) and stored procedures (about half the time spent there) then proceeds to insert into my tables about 40000 records and then updates about 20000 of those records.

It takes 1:15 on my AMD 3.5 Ghz 8-core machine.

Amazingly, if I change those 5 tables such that - Add a BIGINT identity surrogate primary key (the queries still join using GUID) - Demote the prior clustered GUID primary key to a unique column

then it runs in 3:00 minutes!

Changing it from BIGINT to INT gets to about 1:30!

How is it possible that a clustered GUID PK runs significantly faster than an autoincremented INT and much faster than an autoincremented BIGINT clustered PK?

NOTE: the GUID values themselves are generated in code, not by DB.

Check out this simplified benchmark script demonstrating what i mean.

http://pastebin.com/ux5wUJgC

Herman Schoenfeld
  • 8,464
  • 4
  • 38
  • 49
  • Can you provide a simple runnable script that reproduces these characteristics on your machine? – Martin Smith May 21 '13 at 15:39
  • @MartinSmith: yes check out http://pastebin.com/G6BszR66 Clearly clustered guid pk wins. – Herman Schoenfeld May 21 '13 at 15:58
  • Ah right. Not very surprising. There are two indexes to maintain in the `int/bigint` cases rather than just one. And the non clustered Guid one will have exactly the same fragmentation issues as the clustered Guid one does. – Martin Smith May 21 '13 at 16:07
  • @MartinSmith: still slower with two indexes each.. also tested on sql server 2008 enterprise, still slower... oh well – Herman Schoenfeld May 22 '13 at 00:06
  • 1
    It shouldn't take you over a minute to insert then update less than 100,000 records. That's a really, really long time. There should also be no performance impact by having the foreign key references between tables. You should open a new StackOverflow question with the contents of your sprocs and table schema. There's something funky going on there, and you shouldn't have to do this: `To make it efficient, I've dropped foreign-keys between the tables.` – tommy_o May 23 '13 at 15:59

2 Answers2

1

Using your test cases, this is expected. The first test only grows a table with one field. The other two build two columns and two indexes.

Here is a more appropriate test. All three tests have a GUID field and an INT (or BIGINT) field. All fields are indexed. The test table with a PK on an INT with a nonclustered index on the UID is faster by 2 seconds on my server.

Here is my test code: http://pastebin.com/MFTA3Da1

tommy_o
  • 3,640
  • 3
  • 29
  • 33
  • I also added `WITH (FILLFACTOR = 90)` to indexes on GUIDs. It will help avoid index splits as the index grows. – tommy_o May 21 '13 at 17:34
  • The GUID clustered PK is still faster by 3 seconds over INT IDENTITY on my test after adding 2 indexes as you've specified - http://pastebin.com/ux5wUJgC . Looks like the people attacking guid-primary keys didn't have a clue. Oh well, gotta rollback bunch of changes now to use straight GUID pk's. – Herman Schoenfeld May 22 '13 at 00:12
  • If you remove the transaction handling, create the tables before the test, and execute, this is the opposite on my server (SQL Server 2008 R2 Enterprise, 16 core, etc etc). – tommy_o May 22 '13 at 13:27
  • the difference is miniscule. The fact of the matter is clustered GUID keys by themselves are better than or on par with the performance of a clustered identity + non-clustered GUID index. – Herman Schoenfeld May 23 '13 at 06:54
-3

After much testing, it turns out that using guid pk is faster than int surrogate key and a guid natural key.

The talk about avoiding GUID primary keys due to clustering and fragmentation is of little utility since if you're talking about GUID identifiers in the first place, then it's likely that the GUID is intrinsic to the data model and must be stored in the data model anyway, so clearly a single GUID primary key is the simplest and fastest option (by far).

In a nutshell - if you need to identify records with guids then their key should be the guid!

Herman Schoenfeld
  • 8,464
  • 4
  • 38
  • 49
  • You misunderstand the issues surrounding index fragmentation on a clustered GUID primary key. There are 2 issues: one, a GUID is near-random, so without filli factor, the B-tree of the index will constantly split. This is very bad, but can be largely overcome by a high fill factor. The second issue is that each other index on your table must also retain that 16-byte pointed to the PK, instead of a 4-byte data type of INT. If all you're doing is storing a table with GUIDs and *only testing insert speed*, then just PK the GUID with fill. – tommy_o May 22 '13 at 13:33
  • This blog post gives a good run-down of pros/cons on clustered PKs on GUIDs for anyone interested: http://blogs.msdn.com/b/dbrowne/archive/2012/06/26/good-page-splits-and-sequential-guid-key-generation.aspx – tommy_o May 22 '13 at 15:52
  • @tommy_o: It's not me who misunderstands your view, it's SQL Server itself. I ran EXTENSIVE tests on 2008/2012 and noticed FILLFACTOR had 0% impact. That blog suggests making the GUID a non-clustered key, but this means I need an extra clustered identity key which slows everything down by a factor of 2. It might be helpful if you can verify your claims with some (reproducable) evidence. I wasted 2 days tweaking the database just to restore it back. – Herman Schoenfeld May 23 '13 at 06:52
  • @tommy_o: i also varied the tests with PAD_INDEX ON and had 0% impact. – Herman Schoenfeld May 23 '13 at 07:00
  • Microsoft's blog does a fine job of explaining it. Your tests aren't large enough for one, and your test GUIDs need to be generated on multiple sources for it to insert in the middle of the B-tree. – tommy_o May 23 '13 at 13:00
  • To anyone who's wondering why GUID turns out to be faster is because having 1 slow guid key is still faster than having 1 fast autoincrement pk and 1 slow guid unique column. So if your records NEED to carry a guid then it makes no sense to not use it as the PK. If you don't need a guid then autoincrement is a better choice - but in real world, guid's are in the db because it's intrinsic to the data model in most cases (I imagine) – Herman Schoenfeld Sep 28 '13 at 04:04