question of performance

  • Thread starter Thread starter Nemo
  • Start date Start date
N

Nemo

Hello Folks,
about the performance(speed) of a query, does it make difference if I define
my field type as string or integer. Thanks.
 
Hello Folks,
about the performance(speed) of a query, does it make difference if I define
my field type as string or integer. Thanks.
Click to expand...

Not much difference; a 230 byte string will sort and search more
slowly than a 4-byte long integer.

Indexing the fields will make for MUCH more improvement than twiddling
the size of the field.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Nemo said:
Hello Folks,
about the performance(speed) of a query, does it make difference if I define
my field type as string or integer. Thanks.
Click to expand...


Integers tend to have a faster retrieval time 'cuz they tend to be
smaller (1 to 16 bytes).
 
hi
that depends on what data type your field is.
if alpha characters and you dim is as integer, you will
get data type error messages
other than that, no difference. all you do when you
declare a dim is have VB reserve a certain amount of
memory. the amount of memory reserved depends on the data
type.
 
Integer values can fit into registers on a chip. Strings are always pointers
to locations in memory, and operations must be performed against string data
brought to the chip's cache, other hardware or software cache, or bussed in
from way out of town.

I've always wondered why *anyone* would recommend using strings as primary
keys. I realize they can make primary keys more "readable" if built up like
a code, and I realize vendors like Siebel issue text-based primary keys in
100-block increments to keep their primary keys unique among all tables. I
also realize the official stance from some of the mvp's on this board is to
use text-based keys (*sigh*).

But think about it. If you're comparing millions of keys to build a
Cartesian Product Table (i.e., build your query), doesn't it make more sense
to use something that can literally sit on the chip in a register and be
compared in one CMP operation (one op to move the base integer to a register,
one op to move the other integer to a register, one op to tell the chip to
compare the two, done), rather than having to point to memory for the data to
begin the comparison?

Another thing -- (long) integers are 4 bytes, period. I don't think I've
seen character-based primary keys this short. Not only that, these guys are
likely unicode, so each character is two bytes of not compressed (and if so,
perhaps requires translation before comparisons can be undertaken). Smaller
indexes = faster scans = quicker results.

Use Long Integers / Autonumbers for primary keys. Use indexes. Watch your
queries scream. And if you want, look at www.intel.com and read about the
Intel architecture.

David Atkins, MCP
 
Not going to start a war over this, but I agree with you in theory that integer
values are handled quicker. HOWEVER, in most cases the user won't ever see the
difference in performance.

If the number of records _selected_ is fairly large (100,000 and up), you might
see a difference in performance, especially with non-indexed fields. That said,
I tend to use integers as my primary keys.

On the other hand, the OP didn't say the fields he/she was defining were key
fields. So, my answer to that is if the value you are storing is all numeric
characters and you aren't going to be doing math of some type on the values,
then use a string to store the value. That way you don't lose leading zeroes
and if you want you can store the formatting with the "number".
 
I don't disagree with what you said, John.

Specifically, with smaller databases on smaller queries, I would agree the
user would see virtually no difference in execution speed. I may, however,
disagree slightly that the number of _selected_ records would need to be
large. What would be required is the building of a significant CPT, where
the joins would need to be processed before application of a WHERE statement.
I most certainly agree text-based keys would exacerbate the issue if they
weren't indexed by orders of magnitude due to differences in join processes
the engine would have to employ as well as additional table scans.

I think we're more on the same page than you think, and I wasn't
specifically aiming my answer at you or anyone else in particular. I do,
however, have a bone to pick with pundits who push form over power without
understanding what they're asking the computer to do.

--Grey
 
Back
Top