Question on data types

[PaulSpell]

I have a table (which holds several hundred thousand records) with a 6 character string field that acts as a reference and forms part of the indexing for the table (it is not the primary key).

Would there be any real benefit, in terms of performance, in changing the data type to long (this would also involve changing about 25 related tables as well)?

 

[FoFa]

In theory there would be a performance benefit, and the reason you ask, well a long is basically stored as a binary value, and I beleive it is 4 bytes (going off the top of my head here). If your 6 character string is an index, it is storing, and matching on 2 extra bytes. There is just more "overhead" with the string data. However in the real world, sometime the performance gains are not enough to justify that in itself. You may change everything and then, see no difference (from a human percpective). So unless you have a compelling reason, I would leave well enough alone. The "if it aint broke, don't fix it" theroy.

 

[PaulSpell]

Thanks, that pretty much bears out my thinking except for one thing.

When you set the data type of a field to string, even if it's length is only 6 chars, does Access still reserve for the full 255 chars in memory or only the 6 you have specified? I have a nagging doubt about this in the back of my mind.

If Access reserves for only the 6 then I would agree with you that any improvement in performance is likely to be negligible, but otherwise it looks like I will have to change this field in all related tables.

 

[IgorB]

Access still reserve for the full 255 chars.
You can set(type) size limit in the field property.
If you need more than 255 -- choose Memo data type.

 

[PaulSpell]

So to be clear, even if I set the type size limit to 6, Access still reserves for 255?

 

[pono1]

More Bytes, More to Chew On

The Help file says that by default Access versions 2k and 2002 use 2 bytes in storage for each character in a text field (see Unicode or Compression in A2K Help) while Access 97 uses 1 byte per character by default.

So with 6 characters you will be using anywhere from 6 to 12 bytes of storage per record, per field, when using a text data type -- maybe more given what Pat wrote regarding the extra byte for overhead.

Long type, as FoFa said, uses 4 bytes.

Roughly speaking, you can break down performance into two categories. Data processing. Network traffic.

By default, Access 2K+ compresses text and memo fields -- while the Access 2K Help file says this will "ensure optimal performance" it surely must also require some extra processing. Also, if more bytes are needed to store the same data, this too will require more I/O processing than otherwise.

So even assuming that the same amount of data gets thrown into RAM and sent across the network wire whether you store 6 characters as Long or Text, the I/O processing of your app may, in this instance, be negatively affected when using Text over Long.

Will there be a meaningful performance difference if you make this change? Like you and others have said: Don't know. Maybe the best thing, if you have the time and resources and interest, would be to experiment with a copy of your DB.

Regards,
Tim

 

[PaulSpell]

Thanks for the replies, that clears up the doubts I had and confirms that it probably isn't worth the effort in making so many changes.