Performance Problem of Query

[robin9876]

In an access 2000 format database that links to another access database
that has a table with about 30,000 records. When viewing the table in
datasheet mode the last record can be selected with in a few seconds.
On query that uses this base table and joins to about 8 other tables
for lookup values the query takes over a few minutes before it is
stopped. During this time the CPU is at 100% and there is a large
amount of memory available.
The tables all have primary keys and various indexes specified. The
query has been run from Access 2000 (sr1 and sr3) and Access 2003
(release and sp1).

What other perfomance options can be modified to improve the
performance of this query?

 

[Jeff Boyce]

Robin

Joining 8 or more tables can take a while... as you've found.

Have you made sure that every selection, sort and join field is indexed?

Something that's worked for me (sometimes) to speed up a complex query is to
break it apart into steps and use one query for step 1, use that query plus
additional table(s) for step 2, and so on, chaining the queries together.

Regards

Jeff Boyce
<Office/Access MVP>

 

[robin9876]

I have only indexed fields which are joined on. I thought that as long
as these where indexed it should need to have indexes on fields where
the data is being selected?

 

[Jeff Boyce]

The joins need indexing -- and you've got that.

The selection criteria may also benefit from indexing -- this is part art,
part science.

The sort by fields may also benefit -- part art, again.

For the latter two, try & test!

Regards

Jeff Boyce
<Office/Access MVP>