Optimising Database

[parry]

Hi all, I have a database that imports text files every month to update customer records. There are quite a few queries run to obtain statistical information and these are displayed in forms.

The database is getting quite large and is quite slow. Could you please advise what things I should look at to try and speed the database up besides just compacting.

I was thinking of somehow amalgamting the queries into tables by running a make table command but I will have to add data each month which is going to be a pain.

I just wanted some general principals in sppeding up databases and what makes a difference.

TIA

 

[dk]

Hello mate,

This is a HUGE question and entire volumes have been dedicated to just single aspects of doing this. Anyway, here are some steps which you may want to try:-

1. Use the smallest and least complex data types for each field. Access will normally default everything to either Long Integer (number) or Text. If a field in your database can be numeric but is currently text then change it to numeric. If you have a text field, work out what the maximum length of that field could possibly be and set the field length to that value. If your field uses long integer, but could use integer then use that. If it uses a real number data type (e.g. double) but could use integer then use that.....Get the idea?

2. Normalise your data. Attempt to normalise your data. This is harder when you're attempting to change a database that already exists, rather than one that you're designing. The most useful normalisation you could carry out is to ensure that any duplications are removed. You will need to post your table layout and some sample data.

3. Index appropriately. Adding indexes to your table can help speed up sorts and aggregating, but will increase the size of your database and slow any updates. If you find yourself running queries and you're sorting on particular fields then consider indexing that field.

4. Rationalise your data. If you have data which is rarely queried e.g. a prior year, then consider moving it to another table or database.

Hope that gives you a start.

 

[parry]

Thanks DK, I knew it was a pretty open question but you've given me some good basics to look at.