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
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