0

I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the future) used to synchronize the databases and tables and it uses the UUID column for it. I am having a few issues with performance.

Update: The UUID is supplied from the application end - not generated during data load.

Based on the research I have done (here is where I got much clarity on the issue Thank you! Thank you! )
https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega][1]

Here is the takeaway-

  • UUID as PK is ok- as long as it is is NOT Clustered
  • Do not create a clustered index on columns if you are not going to search by the columns
  • Identity (bigint) + HostName much efficient as a clustered index

I am currently planning on designing to include an Identity , DatabaseServerName and Date as a clustered Index and the UUID as a primary key only.

Here are my questions:-

  • since I DO WANT to enforce FK using the UUID columns - do I still create a separate clustered index on identity?
  • All the joins are going to be based on the UUID- will I still benefit from the clustered index?
  • Should I rethink joins on the UUID?If so- how should I go about it?
  • I think your second bullet point should be "filter or join" instead of search. The (can be) used in joins, as they are to evaluate predicates. – scsimon Aug 24 '18 at 18:47
1

I disagree with some of that advice. GUID as a clustered PK is ok as long as you use sequential GUID generation, eg NEWSEQUENTIALID().

Sequential GUID generation, even if only "locally" sequential, mitigates the bulk of the performance problems you will experience with random GUID generation. The alternative designs end up just needing too many indexes.

You need GUIDs for distributed key generation, and that's never going to be as efficient as using int, but the uniqueidentifier column is a requirement for Merge Replication. So your tables can simply be

create table SomeTable(Id uniqueidentifier rowguidcol default newsequentialid(), . . .)
  • Browne-Microsoft : Thanks for the answer. But I just found out that the UUID column is supplied from the application end. I will update it in my question. These are not populated from the DB end. Also I read that using newsequentialid () doesn't actually generate sequential ID so fragmentation is still an issue.....Not sure if I got that wrong. – abbhey Aug 24 '18 at 19:16
  • "I am having a few issues with performance." can you elaborate? – David Browne - Microsoft Aug 24 '18 at 19:49
  • Browne: The rendering is slow on the application end due to the joins on tables with UUID as PK - Clustered. Also , currently facing issue with replicating data to one of the subscriber - one of the table under the article joins on around 7 tables based on UUID- but could also be because of network latency... – abbhey Aug 24 '18 at 19:56
  • Slowness in query processing is not typically caused by GUID keys. You should diagnose the query slowness, perhaps posting a new question with the table DDL, query plans and execution statistics, before redesigning the tables. – David Browne - Microsoft Aug 24 '18 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.