Database Tuning

Topic

New Reply

I have been asked to help tune an Access 2003 database. Right now the database functions, but when trying to do some of the functions for the full company, the database will hang.

Does anyone have suggestions of things to look for or a resource that would help with tuning this monster?

Will adding indexes to the tables help? (some tables have primary keys, some do not)
Will making sure all the related fields have the same data type make a difference? (Store Number in one table is a double, in another it is a long integer)
Should I build the relationships between the tables? (currently the only relationships are in the queries)
Which works better, Make Table queries or a combination of Delete and Append to rebuild the data?

Thanks for any help with this tuning.

Reply | Quote

Replies

This is a great resource on this subject.

Reply | Quote

Thank you, I will check it out.

Reply | Quote

In addition to John’s great resource, most of the things you suggest would be appropriate. All tables should have a primary key at a minimum. And you want to have indexes on fields that use a lookup table if you are trying to filter and such values. Note that Access does add some indexes without telling you, so check before you apply indexes in addition to the primary key. Finally, when you say Access hangs, does it literally stop working and you have to kill it with task manager, or does it take many minutes to complete the function?

Reply | Quote