Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    35

    Unanswered: Record IDs: Numeric vs. Alphanumeric

    Which is faster to use when joining tables by ID (hundreds of thousands of entries)?

    I would like to use alphanumeric IDs because I have different ID types (then I can use a character to denote the type, e.g. C1, C2...Cn ; H1, H2, ...Hn.), but do not want to slow down my queries.

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Create two fields, one stores an autonumber integer and the next one use for your own identification and the other can be a "secret" to the user, perform all joins on this field.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    No real difference as long as they're indexed. I think (but not demostrated) that numeric might be a bit faster ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    From experience you should always work with an automunber field that would be an integer, i dont think there would be much of a performance issue but it would help maintain referencial integrity.

    I always use two fields, one for my own purpose, ie Sales Order Number and an autonumber. That way you can control the Sales Order Number and the database controls the internal referencing.
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

Posting Permissions

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