CODEWITHSUNDEEP
sqlprimary-keyperformanceguidclustered-index
TheVillageIdiot
TheVillageIdiot

Reputation: 40507

Improving performance of cluster index GUID primary key

I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient.

Upvotes: 10

Views: 17004

Answers (6)

bytedev
bytedev

Reputation: 9109

There is no problem with using a GUID as the primary key. Just make sure that when you actually set the GUID to be the primary key then set the index it automatically creates to be of type Non-clustered. A lot of people forget (or dont know) to do this in SQL Server.

NEVER use a clustered index on a GUID. This will cause a physical ordering around the GUID on disk, which is obviously pointless (as others have already pointed out)

Upvotes: 7

mrjoltcola
mrjoltcola

Reputation: 20842

You need to analyze your query. We can only guess why your queries perform badly without viewing the execution plan (which you can get quiet easily from SQL Server or Oracle).

Considering that a GUID is a 128-bit value (if stored raw), a GUID cuts the density of the data and index blocks by as much as 50% (in the case of the primary key index) so make sure GUID is appropriate.

But that might not be the problem, so review the query plan. It could be several other issues.

Upvotes: 1

rafidheen
rafidheen

Reputation: 1842

Please avoid creating clustered index for lenghty string columns. GUID will have 36 char. It will reduce the query performance even you have created as clustered index. for better practice, use integer identity columns.

Upvotes: -5

SQLMenace
SQLMenace

Reputation: 135011

You need to use newsequentialid() instead see here Some Simple Code To Show The Difference Between Newid And Newsequentialid

Upvotes: 5

dwc
dwc

Reputation: 24890

A clustered index on GUID is not a good design. The very nature of GUID is that it's random, while a clustered index physically orders the records by the key. The two things are completely at odds. For every insert SQL has to reorder the records on disk! Remove clustering from this index!

The time to use clustering is when you have a "natural" order to the data: time inserted, account number, etc. For time fields, clustering is almost free. For account number, it might be free or cheap (when account numbers are assigned sequentially).

While there may be technical ways around the GUID issue, the best idea is to understand when to use clustering.

Upvotes: 42

Dave Swersky
Dave Swersky

Reputation: 34810

You can try sequential GUIDS, which will make the index more effective. Info here.

Upvotes: 2

Related Questions