GUID Keys and DataBase Performance

http://(www.informit.com/articles/article.asp?p=25862&seqNum=5&rl=1)

Let's say that we plan to use GUIDs for the primary keys. What are the advantages and disadvantages when compared to INTEGERs? We should always start with the positive, so here goes:

·         We get a more object ID–like datatype because each primary key typically will be unique for the complete database. This gives good effects, for example, when designing a custom solution for pessimistic locking (but that's another long story).

·         @@IDENTITY has been a source of problems when INSERT triggers have INSERTed rows into other tables with IDENTITYs. (You can use SCOPE_IDENTITY() in SQL Server 2000 to avoid this problem.)

·         You can move the creation processing of the values from the data tier to the business or consumer tiers. For example, the consumer can create complete structures of orders and details that can be sent all the way through the business tier to the data tier in one go, which can reduce the number of round trips. A more basic way of saying this is that you can get the ID value before you use it, not just afterward, as is the case with IDENTITYs.

NOTE

In the case of .NET and ADO.NET, it's a big advantage to know the ID value at the time rows are added to the DataSet. Otherwise, for example, autogenerated INSERTs can't be used for master-detail relationships within the DataSet.

·         The span of values for GUIDs is enough for most scenarios. INTEGERs are not big enough for some scenarios. (By the way, with INTEGERs, you don't have to start the seed on 1. You can just as easily start it on the smallest negative INTEGER and thereby double the span of values.)

·         You can prepare for merge replication. When merge replication is to be used, each table to be replicated must have a GUID column (called ROWGUID in SQL Server). You also don't have to give each server in a merge replication scenario a unique set of values to use for the keys. The risk of duplicate GUIDs is so small that this shouldn't be a problem.

·         IDENTITY can't be used for tables in distributed partitioned views.

·         A GUID will not mean anything to the users. An INTEGER can be read, understood, and remembered. It's common to see INTEGER keys in the UI, and that might cause problems later.

·         You know what a nightmare it can be when doing a manual merge between two tables with INTEGERs as primary keys. Not only do you have to create a new sequence for the union of both tables, but you also must change all the foreign keys for all the dependent tables. If GUIDs are used, this sort of problem does not arise.

NOTE

The algorithm for generating GUIDs doesn't use the MAC address of the network cards in recent Windows versions anymore. Instead, it just creates a random value. In theory, this presents a risk of getting duplicate GUIDs, but, in practice, it shouldn't be a problem.

The reason for excluding the use of the MAC address is that it not only couples users to GUIDs, but some network cards don't use unique MAC addresses.

I will discuss this further when we reach the section about the COMBs. Stay tuned.

I'm not a salesman, so here are a few drawbacks:

·         There is a huge overhead in size. As I said, a GUID is four times larger than an INT. This is very important when it comes to indexes.

·         Portability can be a problem because not every database has GUID as a native datatype.

·         You can't use GUIDs with aggregates such as MIN. This is unfortunate when it comes to using WHILE plus an aggregate function, such as MIN or MAX for looping instead of a CURSOR. (Well, a workaround is to use a CONVERT or a CAST to CHAR(36) or BINARY(16) of the GUID before it's used in the aggregate, but watch out for performance problems with large tables.)

·         It's detrimental to developer-friendliness because it's really hard to edit GUIDs manually, to remember them when you browse the database, and so on. You have to get used to navigating the database by using SQL scripts, but this is not usually that much of a problem.

So, the overhead related to size is the main problem.

He goes on to test performance as described in the next section, GUID.COMBS

==== GUID.COMBS =========

COMBS are a transform on ordinary GUIDs that result in a sequential Guids, thus overcoming the SQL insert problems caused by random Guids.

GUID.COMB stands for "COMBination of Guid and the current datetime".

COMBS are still unique but they are not random; they always increase and that means there will be far fewer occasions when SQL has to shuffle the pages to insert a record between two others.

BTW, it appears that SQL Server 2005's newsequentialid() appears to be like a COMB. Unfortunately, it is determined on the data tier, not the client, which means it is no help in off-line scenarios.

[For more on SQL Server 2005's newsequentialid(), see

http://sqljunkies.com/Article/4067A1B1-C31C-4EAF-86C3-80513451FC03.scuk]

Jimmy Nilsson's article http://(www.informit.com/articles/article.asp?p=25862&seqNum=7&rl=1)

describes tests performed on the data tier that compared GUIDs, COMBs, and Identity+Int. These were tests for:

• Inserts of 500,000 records
• Fetches by pk
• Joins on the 16-byte Guid/Comb for. key v. the 4 byte integer

In sum, the COMBs are very competitive on all three tests - about 1/10th slower than the integer in each test. BTW, the GUID insert was 30x the integer insert - which aligns with the described in the other article above for the 4 million rows.

-----------------

Here are three ways to calculate COMBS on the client:

Here's some C# code to create a COMB, I think. One issue is that while the resolution for a DATETIME in SQL Server is 1/300th of a second, the resolution for a DateTime in .NET is 1/10000th of a second (100 nanoseconds). This means that the sequential part of the COMB will change more frequently than in the SQL Server version, but I'm not sure if this is a good or a bad thing. If it could have a negative impact, it would be a relatively simple matter to use the six bytes corresponding to SQL Server's 1/300th of a second instead of the last six bytes of the .NET DateTime object.

public static Guid NewComb() {
byte[] dateBytes = BitConverter.GetBytes(DateTime.Now.Ticks);
byte[] guidBytes = Guid.NewGuid().ToByteArray();
// copy the last six bytes from the date to the last six bytes of the GUID
Array.Copy(dateBytes, dateBytes.Length - 7, guidBytes, guidBytes.Length - 7, 6);
return new Guid(guidBytes);
}

C# Code to mirror SQL ServerBy glapointe, Aug 16, 2002 08:10 AM

The following code creates a comb that will match exactly (or pretty darn close) the combs created in SQL Server. I also included a little method to retrieve the date from the comb.

public static Guid NewComb() {
byte[] guidArray = System.Guid.NewGuid().ToByteArray();

DateTime baseDate = new DateTime(1900,1,1);
DateTime now = DateTime.Now;

// Get the days and milliseconds which will be used to build the byte string
TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);
TimeSpan msecs = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks));

// Convert to a byte array
// Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333
byte[] daysArray = BitConverter.GetBytes(days.Days);
byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333));

// Reverse the bytes to match SQL Servers ordering
Array.Reverse(daysArray);
Array.Reverse(msecsArray);

// Copy the bytes into the guid
Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2);
Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4);

return new System.Guid(guidArray);
}

public static DateTime GetDateFromComb(System.Guid guid) {
DateTime baseDate = new DateTime(1900,1,1);
byte[] daysArray = new byte[4];
byte[] msecsArray = new byte[4];
byte[] guidArray = guid.ToByteArray();

// Copy the date parts of the guid to the respective byte arrays.
Array.Copy(guidArray, guidArray.Length - 6, daysArray, 2, 2);
Array.Copy(guidArray, guidArray.Length - 4, msecsArray, 0, 4);

// Reverse the arrays to put them into the appropriate order
Array.Reverse(daysArray);
Array.Reverse(msecsArray);

// Convert the bytes to ints
int days = BitConverter.ToInt32(daysArray, 0);
int msecs = BitConverter.ToInt32(msecsArray, 0);

DateTime date = baseDate.AddDays(days);
date = date.AddMilliseconds(msecs * 3.333333);

return date;
}

Here is one in VB:

Public Shared Function NewComb() As Guid

        'Descr/Purpose: Create a COMB. (A COMBination of an

        '               ordinary GUID and the current datetime.

        '

        '               The current datetime should be in

        '               SQL Server format. That is days after

        '               1st of Jan 1900 and the no of milliseconds

        '               after midnight, divided by 3.3333...

        '

        '               It is the lowest six bytes of the GUID

        '               that get exchanged for the current datetime.

        'Parameters:    -

        'Return:        A new GUID (COMB)

        'Date, who:     2002-04-04, Jimmy Nilsson

        'Revisions:

        '2002-04-07, Jimmy Nilsson

        'Changed the solution to use a bytearray instead

        'of a string.

        Dim i As Short

        Dim theNow As DateTime = System.DateTime.Now

        'Calculate the days after 1st of Jan 1900.

        Dim theDays As Integer = theNow.Subtract _

        (New System.DateTime(1900, 1, 1)).Days

        'Calculate the number of milliseconds after

        'midnight, divided by 3.3333...

        Dim theMilliSeconds As Integer = CType(theNow.Subtract _

        (System.DateTime.Today).TotalMilliseconds _

        / (10 / 3), Integer)

        'Create a new GUID and save it as a bytearray.

        Dim aBuffer As Byte() = Guid.NewGuid().ToByteArray()

        'Exchange byte 10 and 11 for the current days from

        '1900-01-01.

        Dim theDaysAsBytes As Byte() = BitConverter.GetBytes(theDays)

        For i = 0 To 1

            aBuffer(10 + i) = theDaysAsBytes(1 - i)

        Next

        'Exchange byte 12-15 for the no of milliseconds after

        'midnight.

        '(Divided by 3.3333...)

        Dim theMilliSecondsAsBytes As Byte() = _

        BitConverter.GetBytes(theMilliSeconds)

        For i = 0 To 3

            aBuffer(12 + i) = theMilliSecondsAsBytes(3 - i)

        Next

        'Return the bytearray as a new guid.

        Return New Guid(aBuffer)

    End Function

=========================

Other sources on this topic:

http://www.yafla.com/papers/SQL_Server_Performance/high_performance_sql_server_pt2_1.htm