Re: run multiple queries in access

zwasdl@xxxxxxxxx wrote:
Some of my queries take 1 hour to run, can I run other queries in
access while waiting on the first query? If so, how can I do it?

Thanks a lot,

Wei

Can you tell us why a query takes 1 hour to run? Is it due to extremely large tables?

Some things to speed up a query are indexes. Let's say you have an Orders table and an Employee table. The employee table's key is EmpID. The order table has EmpID for the person who took the order. The EmpID should be indexed.

Also, fields that are commonly filtered (see Where clause) should be indexed. "Where OrderDate = Date()"...index on OrderDate.

Now, some will disagree with me here, but SubSelects suck wind big time 99% of the time. And I'd almost wager you have a subselect or two in your query.

I might take a look at your query and see if I could create another query that you get a list small. Ex:
Select Orders.* From Orders Where OrderDate = Date()
and save that as Query1
Now create a new query. Add Query1 and link to other tables or queries and save as Query2.

It's possible that other tables you are linking to can also be made into other queries that produce smaller recordsets. I would study your query and determine if you can make other parts of the query separate queries themselves. I've taken queries that take 1/2 - 1 hour to calc down to seconds using the above concept.

In one of those long ones I created QueryStep1 to create a smaller subset. Then had a QueryStep2 using QueryStep1 to get a smaller set then had a QueryStep3 using QueryStep2 and then finally a QueryStep4 using QueryStep3. Going from a long calc to one done in a second or 2 is worth the effort.


.

Relevant Pages

  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)