10

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.

TheVillageIdiot
  • 40,053
  • 20
  • 133
  • 188
  • 1
    Possible duplicate of [What are the best practices for using a GUID as a primary key, specifically regarding performance?](http://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega) – AHiggins Jan 07 '16 at 14:19
  • Make it not clustered! (if it has to stay as a GUID) – bytedev Mar 30 '16 at 15:36

6 Answers6

42

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.

dwc
  • 24,196
  • 7
  • 44
  • 55
7

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)

bytedev
  • 8,252
  • 4
  • 48
  • 56
  • 1
    NEVER use a clustered index on a GUID? That is a bold statement. Are you sure there are no counter examples? – MarkPflug Oct 01 '10 at 23:24
  • 1
    Please name one if you know one :-). I see no reason why you would want to physically order something around a GUID. – bytedev Nov 03 '10 at 14:29
  • 1
    @IainFraser and how will physically ordering around the GUID on disk speed your SELECT up? Each GUID is supposed to be unqiue "over space and time" and so is not related to any other GUID. (This assumes you arent using sequential GUIDs, which many would argue are not real GUIDs in the first place). – bytedev Mar 30 '16 at 15:34
  • @nashwan You forget that not using clustered index on the uniqueidentifier PK will add a key/RID lookup to some queries on select, update, and delete operations. This is not as clearcut as you make out. Certainly I wouldn't choose this design at the start, but if you are looking at an existing design, changing it is not straightforward. Insert performance is not the only factor. – JamesB Nov 07 '16 at 13:16
5

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • 3
    I have read about newsequentialid() a lot but it seems to be a SQL Function, Can anyone tell me what to do if Guid are being generated from Code (C#). – Mubashar Feb 11 '10 at 08:12
  • 1
    -1 What has this answer got to do with physical clustering of indexes? The person asking the question has already stated he/she has 10K+ rows and he/she probably doesnt want to go and change all the keys. – bytedev Mar 30 '16 at 15:38
2

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

Dave Swersky
  • 34,502
  • 9
  • 78
  • 118
1

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.

codenheim
  • 20,467
  • 1
  • 59
  • 80
-5

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.

rafidheen
  • 1,783
  • 1
  • 10
  • 13
  • 2
    Guid is 16 bytes - not 36 chars. – Josip Medved Sep 07 '10 at 19:43
  • Kimberly Tripp discusses this issue for large tables very well on her site at this link: [GUIDs as PRIMARY KEYs and/or the clustering key](http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx). – Graeme Sep 24 '12 at 20:52
  • @Josip: I doesn't mean the size of GUID. 36 Char is length of GUID. – rafidheen Dec 08 '15 at 10:11