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 may make things slow down in some cases.

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

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

I have one database big like yours. Yes, everything is slow, fortunately my client is very patient. I think that for such an amount of data, Access is too small. I would develop with a more powerful database like Oracle, SQL Server, Sybase, etc..

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.

I don’t about Access 2002, but I’ve found 2000 slower (!) than 97, as it uses much more memory. 256Mb may be allright, but not for 1 mio records.

Regards.