Most efficient data type for UUID in database besides a native UUID.

Question

What would be the most efficient data type to store a UUID/GUID in databases that do not have a native UUID/GUID data type? 2 BIGINTs?

And what would be the most efficient code (C# preferred) to convert to and from a GUID to that type?

Thanks.

Answer 1

Looking at the .NET guid class, there are a couple ways to initialize a guid:

Guid(Int32, Int16, Int16, Byte, Byte, Byte, Byte, Byte, Byte, Byte, Byte) Guid(string)

While it may be more efficient, in theory, to store the integer in a database (you could use bit shifting to really store 4 32-bit integers.. but you'd have to calculate this out when loading and saving every time. Additionally, it would take 4 fields in the database.. I would imagine it would end up being less efficient.

Add in the impossibility of reading this directly in your database for debugging/testing purposes, and I'd say hands-down that it's best to store a string. It's only a 32-character field (36 if you include the dashes), and it's very easy to convert. guid.ToString() and new Guid(stringValue);

Answer 2

In my experience, the UUID broken up into two integers will still be more efficient than using a char field. Different DBs react in different ways though. Collation could make a difference there too. That being said, there are usually much bigger performance "sins" all over applications, and I don't think that this would be a major thing either way for many applications. You'll have to judge yourself based off of just how busy is this part of your app going to get? Do you need the absolute fastest querying possibly by UUID? Does 600ns vs 400ns a big time difference to you?

If there's going to be a lot of manual sql doen with the db, then having a key that's comprising a UUID from separate fields kind of stinks when you need to do an insert and theres no db default for it. That's also a problem with chars though.

If you have a database abstraction layer, then combining multiple table fields to get your UUID shouldn't be a big deal.

Answer 3

It's hard to say what would be the most efficient without knowing the database you are using.

My first inclination would be to use a binary(16) column.

As for using that value in C#, the System.Guid type has a constructor that accepts a byte[] array, and a method ToByteArray() that returns a byte array.