Is it necessary to use smaller field sizes for text ?

  • Thread starter Thread starter Dewu Chen
  • Start date Start date
D

Dewu Chen

Hi all,

I created four databases (Text 7, Text 255, Long Integer and Double) for an
experiment. Each database contains a table of one million records (from 1 up
to 1,000,000). The four tables have the same contents but different data
types or field sizes as their database names suggest.

It is not strange that some of them have the same size (see below) because
Access 2003 stores data in a "compact" format.
Text 7: 23,040,000
Text 255 23,040,000
Long Integer 16,191,488
Double2 16,191,488

The HELP tells us "You should use the smallest possible FieldSize property
setting because smaller data sizes can be processed faster and require less
memory."

Nevertheless, the following experiments show that the above claim is not
always
true, especially for the case of TEXT data type.

Speed:
(1)Sorting 1 to 1,000,000 in reverse order
Data type/Field size Time (sec. roughly)
Text/7 14
Text/255 14
Number/Long Integer 8
Number/Double 12
(2)Finding 999999 out of 1 to 1,000,000
Data type/Field size Time (sec. roughly)
Text/7 21
Text/255 21
Number/Long Integer 23
Number/Double 32
Conclusion: It is true for numbers but not for texts.

Memory:
Opening four tables respectively
Data type/Field size Mem. (avg.)
Text/7 34850KB
Text/255 34940KB
Number/Long Integer 34836KB
Number/Double 34616KB
Conclusion: There are no significant differences in memory usage.

My question is:
Is it necessary to use smaller field sizes for text
If the answer is YES, please give me an example.

Thanks in advance for any reply.

Dewu
 
Dewu,

Try to compact them all and repeat your checks, you should get a different
picture, especially in terms of file size. Differences in speed will be more
apparent if the field is NOT indexed (and not a PK, ofcourse), if I'm
guessing right.
If you bother to do it, please post back your findings!

Nikos
 
Hi Nikos,
Thank you for your quick reply. I know Access databases keep growing in
spite of adding or deleting something. So I created four new databases and
imported four tables from an existing database separately. In this case no
compressing is needed. However, I still can't find the reason for using
smaller field sizes for text fields.
Dewu
 
Back
Top