CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
dotnetN00b
dotnetN00b

Reputation: 5131

Which is faster comparing an uniqueidentifier or a string in tsql?

I have a table which holds the guid for an user and their actual name as a string. I would like to grab some information based on an user. But which field should I use? Should my code say:

select * 
from userinboxcount 
where countDate >= startDate and countDate <= endDate and userid = '<guid here>'

or 

select * 
from userinboxcount 
where countDate >= startDate and countDate <= endDate and username = "FirstName LastName"

Upvotes: 5

Views: 6813

Answers (5)

Pankaj
Pankaj

Reputation: 10095

GUID will be good enough.

  1. GUID will produce unique values in the table.
  2. Create Non Clustered Index on this column.

Reference - Non-clustered indexes are particularly handy when we want to return a single row from a table.

Upvotes: 1

David Kent
David Kent

Reputation: 31

Are you completely married to the GUID? You should use a GUID when you need a primary key that will be unique across multiple systems. I would suggest skipping the GUID and using a composite key. For example, you could use an identity plus a GETDATE() as a composite key. This would give you an easy way to query your data (try to remember a GUID over an integer). This will also perform much, much better than GUID. Probably twice as fast.

Upvotes: 1

Guffa
Guffa

Reputation: 700342

The biggest difference is if one field has an index that the database can use, and the other doesn't. If the database has to read all the data in the table to scan for the value, the disk access takes so much resources that the difference in data type is not relevant.

If both fields have indexes, then the index that is smaller would be somewhat faster, because it loads faster, and it's more likely that it remains in the cache.

Ideally you would have an index for all the fields in the condition, which has the fields that you want to return as included fields. That way the query can produce the result from only the index, and doesn't have to read from the actual table at all. You should of course not use select *, but specify the fields that you actually need to return.

Other than that, it would be somewhat faster to compare GUID values because it's a simple numeric comparison and doesn't have to consider lexical rules.

Upvotes: 5

Dan
Dan

Reputation: 5231

If userid is a primary key, you should use that. If you use first and last name, you could have two John Smith entries, for example, and that could create an issue for you. Using the PK should be safer

On the performance side, it's a good idea to become familiar with explain plan (execution path?) of the query. I'd expect using the userid would be faster, but checking the plan should tell you for certain.

Upvotes: 0

aF.
aF.

Reputation: 66697

See the query plan and you can see it for yourself.

But the unique identifier usually has an index and the string (username) might not have. If so, and if there are many records, prolly the unique identifier would be faster!

To the the query plan, check THIS article.

Upvotes: 3

Related Questions