Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by

myID = 'xxx'+convert(varchar(40),newID())

In general its a bit of a mess

What are the performance implications for this design and is it worth re-working the tables to convert the datatypes. Tables are generally around 1/2 M records with a couple table in the 2-4M record range.

The impetus of this question is that I am trying (unsuccessfully) to optimize an proc that joins 24 tables and views and the server is not handling it very well.

Thanks for any insight

share|improve this question
    
Ugh. Can you expand on what 'xxx' is and where it comes from? Is this part important to application logic? –  Jon Seigel Nov 4 '13 at 20:23

3 Answers 3

up vote 2 down vote accepted

In general, it is a bad idea to have long clustered index keys. For that it is not important if the key is made up of several short columns or few long columns. This makes the maintenance of every index as well as the read access of every index on that table a lot more expensive that it has to be. As the PK is by default enforced by a clustered index, you most likely have a very long clustered index key.

Also in general it is a good idea to have you clustered index key be monotonously increasing. As your column is valued based on NEWID it will distribute new rows all over the table causing fragmentation. The extra long key will make this even worse.

And finally, in general it is a good idea to not spend too much time tuning performance preemptively. If you do new development follow best practices (like the two recommendations above). With existing software, fix only the stuff that is causing a headache.

Now it looks like you have that headache already. Therefore I would start by migrating those primary keys to either identity columns (preferred) or to uniqueidentifier columns that default to newsequentialid().

But this is a difficult change to do if you can't take downtime. I that case I would start by adding new indexes to support you current strongest headache (read: stored procedure). Than work on cleaning up the mess afterwards.

share|improve this answer
    
Thanks for the input. You are correct the headache already exists. I will try to "fix" it for now with indexing since (as you correctly guessed) downtime isn't an option. –  Lance Nov 4 '13 at 22:51
    
Why do you prefer identity to uniqueidentifiers? Why do you prefer a monotonically increasing clustered key? –  Greenstone Walker Nov 5 '13 at 2:24
    
@GreenstoneWalker, an INT is 4 bytes - a UNIQUEIDENTIFIER is 16. Shorter key -> better performance. A random key causes a random insert position into existing pages which in turn causes lots of page splits which in turn causes fragmentation. Hence: monotonous key -> better performance. (As with all things SQL Server there are exceptions.) –  Sebastian Meine Nov 5 '13 at 3:47

Using UNIQUEIDENTIFER uses much less space than VARCHAR. This is because UNIQUEIDENTIFER uses only 16 bytes, whereas using VARCHAR uses at a minimum 36 bytes. Based on your situation, yes the performance would be improved and improving the reliablility of your index keys. If you use SQL Servers UNIQUEIDENFIER there is no option for duplication and if you use this in default value then always value is there.

share|improve this answer
1  
To clarify, using a uniqueidentifier does not enforce uniqueness - uniqueness is only enforced by using a unique index. +1, however, for knowing that uniqueidentifier datatype is a 128-bit integer in SQL Server. :-) –  Greenstone Walker Nov 5 '13 at 2:26

If you have the time:

  1. Add a nullable Integer column to each table.
  2. Using Row_Number(), set the Integer column value (ordering by the create-timestamp on the table).
  3. Go back and set the Integer column to non-nullable, and make it an Identify field.
  4. Make this Integer column the clustered-index (if one already exists, then make the old clustered-index a non-clustered one first).
  5. Anywhere this table is referenced as a foreign-key, add a nullable Integer column and set it's value based on the value in the primary-key table.

Here is where you'll need to apply a little more elbow-grease.
If you don't want to bother rewriting all the existing logic that persists data into these legacy tables then:

  1. The identity column you added will set itself, so existing insert/update statements won't need any further re-work unless the table references foreign-keys you have also replaced with integers.
  2. For other tables where this primary-key table is referenced as a foreign key, you could simply add a trigger that populates the foreign-integer-key based on that crazy xxx-guid-varchar you have there.
  3. This should guarantee the foreign-integer-key's you've added to these tables are populated correctly; without the need to alter any existing code that inserts/updates these legacy tables.

Now you can go about "fighting your biggest performance fires" by rewriting your problem queries to join on the new integer fields, instead of those crazy triple-x-guid-varchar's you got going on there.

Good Luck!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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