Query/Table Performance Question: Field Size 255 vs Actual Size(+)

[smedvid]

Has anyone ever performed a benchmark against a MS Access 2007 table that has ~50,000 rows; 15 fields, most of which are Text(255)... versus re-sizing the fields to actual size (or slightly larger)? I did a quick test writing a query with group by; and saw only a ~1 second difference for ~50,000 rows. I really expected to see a better performance. Looking for justification to optimize and re-size DB tables, and need to prove performance gain. Any comments welcomed. tia,

Steve Medvid
IT Consultant & Web Master

 

[dhookom]

IMO, it would take many times longer to adjust the field sizes than to run the query thousands of times.

Duane
Hook'D on Access
MS Access MVP

 

[KornGeek]

If I'm not mistaken (which I very well may be), I believe that Access uses variable-length strings (similar to the SQL varchar) to store text fields. Thus, allocating additional space (such as making the maximum field width 255) has a nominal impact on performance or file size.

Duane is probably right that you will lose far more time changing the field sizes than you would gain on query execution. However, it may still be the right choice. If there's a 1 second impact when you run the query, but it's impacting end-users rather than you, it could be worth doing what you can to streamline things.

Most likely, it's not worth the time to change the field sizes to try to improve performance.

 

[lameid]

I would expect that if you can keep your records smaller there would be performance gain but I think KornGeek is rigt and it will not matter to shorten the size much.

Likely for performance it is already optimized with multiple columns for long text fields. It is conveivable that if you separated out the fields into groups used together and you almost never have to join the tables back together to get data together you may see imporvement because the recordsize is smaller. However the overhead cost of a join is big compared to smaller records (intuition). And likely any gains will be trivial. Indexes and file throughput will probably make the biggest difference. By file throughput I mean how fast file is read and written; often the network is the bottleneck for this unless the file is local.