Why is my query taking so long?

[pingman]

Hi there everyone! I am just curious to know if there is any way to speed up queries. I currently have one that is taking a few minutes rather than seconds to complete. It is an append query that appends another table in the database. Any suggestions? All tables are indexed. Thanks for your suggestions.

Pingman

 

[tlbroadbent]

Query performance can be affected by any number of things.

Number of records affected.
Size of records.
Speed of the PC running the query.
Speed of hard drives.
Network speed if database is on a network drive.
NOTE: Updates, appends and even selects can be slower across network than if the database is on a local drive.
Amount of RAM available.
Amount of unused disk space available.

All these things are obvious and have probably been checked out or can't be changed easily. Less obvious factors include.

Too many indexes on a table when appending records. Having to write multiple indexes can slow append queries considerably.
Table in use in multi-user database. Table or pages of table can be locked causing waits.
Is the table native Access or linked via ODBC? Linked tables can be much slower to append.

It would be helpful to have more information - Access version, OS, linked table or not, etc. You could also help us by posting the query so we could analyze it. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin