Need help speeding up 25 minute query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There are approximately 200,000 records in a line-item table of an invoice
database (access 2000).
My query is tring to sum up total amount for each invoice by adding taxable
and non-taxable items separatly and placing the grand total in a third
column of same query.
When I run this query (without append or add the result to table), it runs
fast (few seconds).
But, when I modify the query to append the result to a new table, it takes
about 25 minuts. It is running on a good stand alone pentinum III computer.
Not on network.
Any sugguestions?
 
Dear Afzal:

Appending rows to another table is rather obviously the problem. That
table will likely have one or more indexes. Each index is being built
as each row is added. Possibly that table is already large, and then
so would the indexes be large. This is likely where much time is
being consumed.

If you remove those indexes temporarily, the rows would add relatively
quite quickly. If you then replace the temporarily dropped indexes,
they would be rebuilt. However, the algorithm to build an entire
index from scratch takes advantage of some capabilities not possible
when adding one row at a time. It is the general experience that this
is often dramatically faster.

That's the best suggestion I have at the moment.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I really don't have to add the result to a table. All I am trying to do is
figure out over-due invoices by matching all invoices' total to their payment
history.

When I use first query as an input to a second query, time delay is same.
 
First, I agree completely with Tom on how to speed up raw appends.

That being said, if you still experience 25-minute lag when using only
queries, even with 200k records, then I'd have to ask what indexes you have
on your base tables, and futher if there is something you are doing that
would cause MS Access to not use an efficient query plan utilizing those
indexes (such as basing it upon a union query).

Sounds to me like your tables are not optimized properly with indexes.

David Atkins, MCP
 
Back
Top