Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Search Speed Int VS Char(36)

    I've been looking at a few of our tables and noticed many are keyed by a guid (char 36) field. I was wondering if it would be faster to replace this field with a unique integer?

    These tables store alot of data and need to be able to find this key row as quickly as possible.

    I could easily make a stored proc to generate a unique integer for entry applications.

    Any ideas?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, integers are smaller and search more quickly than GUIDs, but the difference would only be noticable on very large tables. How many rows are you talking about?

    Be aware that GUIDs have certain advantages that integers do not (such as for merging data from multiple database copies or in reverse-relationship schemas). Make sure your application's functionality does not depend upon the unique qualities of GUIDs before you switch to integers.

    blindman

  3. #3
    Join Date
    Dec 2002
    Posts
    45

    Use of Guid/integer

    This field would be used in the following way:

    We have a master table that stores a commission value and has a foreign key of the guid.
    Date Table that has multiple date ranges for each master row (could = x365 per year)
    Component Table that has multiple components for each master row (could = x30 for each master row)
    City Table that has multiple rows for each master row(could = x2820 different city combos for each master row)

    right now these tables all carry the char 36 , this database design is for commissioning our products, I don't see any data ever being merged. Is integer the wa

  4. #4
    Join Date
    Dec 2002
    Posts
    45

    Use of Guid/integer

    This field would be used in the following way:

    We have a master table that stores a commission value and has a foreign key of the guid.
    Date Table that has multiple date ranges for each master row (could = x365 per year)
    Component Table that has multiple components for each master row (could = x30 for each master row)
    City Table that has multiple rows for each master row(could = x2820 different city combos for each master row)

    right now these tables all carry the char 36 , this database design is for commissioning our products, I don't see any data ever being merged. Is integer the wa

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As long as you don't have code that depends on GUIDs. For instance, I see a lot of threads on this forum where people insert rows into tables with auto-increment keys, and then have to try to find out what keys were assigned. GUIDs allow the interface or a stored procedure to assign the key value (using the NewID() function) before the record is ever created, with confidence that it will not conflict with any existing key values.

    Check your code thoroughly to make sure that it does not use GUID-specific logic.

    Also, certain methods of replication require GUID values, and will add a GUID column to your table if it does not already have one.

    How many rows do you expect in these tables?

    blindman

  6. #6
    Join Date
    Dec 2002
    Posts
    45

    How many rows

    I would expect about 1000 rows in the master table
    and about 1-2 million in each of the sub tables.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You might see performance boost by using integers. You will have to decide whether it is worth the effort of the conversion.

    Good luck.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    even if there is a possibility of seeing any performance improvement, i don't think it's worth the effort. considering the amount of data you have, - i'd say the performance improvement would be marginal, if any. also, it sounds like you have a very decent design in place. just make sure you check all existing indexes actually working for you. don't stop at just confirming that each foreign key is indexed. see if you can take those one-field indexes and convert them into a set of covered indexes based on mostly used queries.

  9. #9
    Join Date
    Dec 2002
    Posts
    45

    Amount of improvement

    This data model hasen't been populated yet, so it would just be a matter of changing the data types in the tables and the procs.

    Wouldn't we see a substantial performance increase as the amount of rows increase? This data structure will get hit about 6,000 times an hour so i figure most of the data will be cached in memory. Taking the key value down from a 36 byte field to a 4 byte field would allow for almost 9 times as much data to be cached. Let me know if there are any holes in my theory.

    Also I'm interest in buying an advanced data modeling /sqlserver book.

    Any recomendations?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Taking the key value down from a 36 byte field to a 4 byte field would allow for almost 9 times as much data to be cached."

    ...yeah, if the only column in your recordset is the key value. But if you've got a dozen or so other columns (integers for example), then you are reducing the rowsize from 84 to 40, or about 50%. Throw in a couple datetime values and some varchars and you will see even less of a difference.

    blindman

  11. #11
    Join Date
    Sep 2003
    Posts
    522
    36 bytes? i thought it takes only 16 bytes to store a guid value. where did 36 come from?

  12. #12
    Join Date
    Dec 2002
    Posts
    45
    Originally posted by blindman
    "Taking the key value down from a 36 byte field to a 4 byte field would allow for almost 9 times as much data to be cached."

    ...yeah, if the only column in your recordset is the key value. But if you've got a dozen or so other columns (integers for example), then you are reducing the rowsize from 84 to 40, or about 50%. Throw in a couple datetime values and some varchars and you will see even less of a difference.

    blindman
    yes u state the obvious, but most tables have a structure like:

    guid int
    field1 char 3
    field2 char 3
    data int

    most normalized tables are getting smaller and carrying a unique key - hence the question.

    Its hard to determine if your answers are in the interest of best practices or do mean some of these things i'm doing are pointless?

    This application will be used to compete with the likes of expedia.com and search speed means the difference between profits or failure.
    I realize that most data structures may not care between a search speed of .01 & .1 but when doing large volume its proven critical.

  13. #13
    Join Date
    Dec 2002
    Posts
    45
    Originally posted by ms_sql_dba
    36 bytes? i thought it takes only 16 bytes to store a guid value. where did 36 come from?
    We are/were using the data type of char(36) not unique Identifier.
    Our guid was being generated in the application.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good catch, ms_sql_dba

    If they are true GUIDs then you can store them in the 16 byte uniqueidentifier datatype. SQL Server translates between GUID character strings and uniqueidentifier values implicitly, so no additional coding is necessary.

    I guess the last word is, if it gives you warm fuzzies and will help you sleep at night, switch to integers. It doesn't sound like you have a commanding reason for using GUIDs.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •