Looking for some advice please....

[Monti2]

I have built an Access database which achieves the objective of taking a number of source files each month, and performing data manipulation (filters, lookups etc), to generate outputs files in the required format.

In order to achive this there are several instances where I used "stacked queries", for example starting out with table1, then creating query1 based on table1, then having query2 based on query1, and query3 based on query2, etc, often getting as far as query5 or so.

Now given that some of my source tables have 250,000 records and 10 or so fields, running these stacked queries seems slow (up to 1 hour).

My question is if I was to make all my queries into make table queries, and then based my next query off the table, would it increase the run speed? Of course I would create a macro to run all the make table queries in the required order. This would mean having a lot more tables in my database, so would I see any improvement, and is it worth it?

Alternatively is there any other way I could improve this situation? I can write a bit of SQL, but don't have any experience of VB.

Thank you for reading, any advice will be appreciated.

Monti.

 

[ColinEssex]

I'll post a reply but I'm on shaky ground with this so bear with me. I know that you'll get a better reply from others but I'll have a bash and start you off.

Firstly, what is the first query doing? is it extracting (for example) records from a given date range?

I think (and this is the shaky bit) that when you run so many "stacked" queries, Access re-runs all the others as well.

It may be an idea to see how long it takes to run the first query on its own. Try running the others with the result of the first query in a makeTable and see if its quicker.

Try re-structuring the queries so that they are not doing so much complicated work (then they'll run quicker)

Running queries for an hour is not really that good.

Hmmmm, just read through this and I'm not sure I've been much help.

Col

 

[Pat Hartman]

Access does not re-run all the queries. You run query5 and Access takes care of the rest and runs the process in the correct order.

If your selection criteria is not already on the innermost query (the one that actually selects the data from the large table), move it there. You want to reduce the recordset as early as possible. You might see some improvement if you make the innermost query a make-table query, but I wouldn't do it to all of them. Try it and see what happens.

As a general rule I avoid temp tables since they bloat the db and make the fe unusable as a shared db on a network.

 

[The_Doc_Man]

I'm reading something into what you said, and if this doesn't actually apply, perhaps you would do better to ignore this. But if I'm right...

If query1 selects items using a WHERE clause, ...

and if query2 selects items from query1 with another WHERE clause, ...

and if query3 selects items from query2 with another WHERE clause, ...

etc.

then combine the WHERE clauses.

Pat suggested moving the selection criteria (the WHERE clause) into the innermost query. I'll generalize that statement. When possible, ALWAYS put the most restrictive criteria in the innermost query. When possible, particularly if the most restrictive criteria involves short values, put an index on the fields involved in the most restrictive WHERE clauses.