Query speed question

[Rabbie]

What I called my unique number actually must be a text because I am informed that the workorder 'number' as I called it can actually be alpha-numeric. I was calling it a number in the 'generic' term but obviously I need to be more accurate in my choice of words.
I suppose a numeric would be faster then alpha-numeric?

A Numeric index will always be faster than a text field index because it uses a smaller field to store the info

Edit: Please excuse typos as I am holiday this week and my laptop keyboard is a bit sticky

 

[Sess]

Thank you. Might sound that I am being too picky now but I want it to not be too slow when there is more then a year of data in the tables.


BWT I have learned that no keyboard enjoys margueritas as much as I do and club soda will not rinse the sticky out.

A Numeric index will always be faster than a text field index because it uses a smaller field to store the info

Edit: Please excuse typos as I am holiday this week and my laptop keyboard is a bit sticky

 

[Galaxiom]

A Numeric index will always be faster than a text field index because it uses a smaller field to store the info

There is actually more than size involved in affecting speed when using a an alphanumeric field as a key and particularly when there is a join or Where clause on the field.

Access is mostly case insensitive so text fields must be compared character by character in both upper and lower cases. To compare number fields, one complete value is simply subtracted from the other. A result of zero means a match.

A ten digit long integer can be compared with four single byte calculations. A ten digit text field can require as many as twenty single byte calculations. That is potentially five times the work, though presumably when the first character does not match, the rest of the test is abandonned and returns false.