FredixBlog

Quertion: I am useing Access 97 to operate a table with about 1 million records, and 30 queries, and about 40 reports, and 10 of these reports use subreports.
When I try to run these reports it is very, very , very SLOW. 5-10 Minutes.
My computer is a 1gig 256Mg Ram 20 Gig HD
If I upgrade to Access 2000 or XP will that speed things Up?
Fredix gave this response on 1/4/2002:
Hi!

There is not much to do with so many records, except focus on indexes to optimize the queries:

1) Where you have a relation between two tables, create an index for the MASTER column. Don’t create an indes on the CHILD column: This makes things slow down.

2) Where you have a selection criteria, create an index.

3) Avoid criterias like: LIKE “*blah”. LIKE “blah*” is all right.

While writing, there are some more improvements ideas:

4) Minimize the field sizes, in order to minimize the table sizes: use Byte datatype for integers if possible. look for the largest text data and set the column size accordingly.

5) Split your tables: Create one for the most used columns, and one for the least used.

6) For a report, create a table with the results and fill it with a delete and an add records query. Base your report on this result table and browsing the report on screen will be faster.

Regards.