Re: Extremely slow IN clause

How much time it takes to run the Query2 ?

Also, make sure that Query2 is running only once and not multiple times (one
time for each possible value of Query 1).

The use of a temporary table may solve your problem.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


"Carl Rapson" <cr@xxxxxxxxxxx> wrote in message
news:%23Jtk6SQRFHA.248@xxxxxxxxxxxxxxxxxxxxxxx
>I have an updateable query, Query1, that includes a join between two
>tables:
>
> SELECT <fields>
> FROM Table2 LEFT JOIN Table1
> ON Table2.ID = Table1.[Master ID];
>
> The ID field is the PK of Table2. My problem is, I don't want to include
> all records from Table2 in my query. I have another query, Query2, which
> generates the desired subset of ID values from Table2. Unfortunately, this
> query has at its heart a MAX aggregate function. As a result, I can't
> simply replace Table2 with Query2 in Query1, since this makes Query1
> non-updateable.
>
> So, the only way I could find to incorporate Query2 into Query1 is to use
> an IN clause:
>
> SELECT * FROM Query1
> WHERE ID IN (SELECT ID FROM Query2);
>
> This works, but the execution is extremely slow. Running Query1 alone
> returns over 4500 records in less than a second; adding the IN clause
> increases the time to 5 minutes (or longer over the network). I'm using
> Query1 as the RecordSource of a form, and this produces unacceptably slow
> response times for my users. Why does the IN clause slow the query down so
> much? Is there a trick to using IN clauses? Os is there another way to do
> the same thing without losing updateability?
>
> Thanks for any information,
>
> Carl Rapson
>
>


.

Relevant Pages

  • RE: loop though records in a query?
    ... do a crosstab query on these records ... Next (next record in query2) ... I have a query called query1. ... I also have query2 which is a list of unique records in query1 based on ...
    (microsoft.public.access.queries)
  • Re: How do you pass parameters between queries in Microso
    ... It sounds like you might have one query that depends on the ... and two queries (QUERY1, QUERY2). ... user retrieves formatted records within a particular date range by ... QUERY1 references the text boxes in FORM1 where a data range is entered. ...
    (microsoft.public.access.queries)
  • RE: loop though records in a query?
    ... For each group of records in query1 that are related to the record in query2: ... I have created the query with the joins like you suggested. ... Unionthe crosstab to a master crosstab query ...
    (microsoft.public.access.queries)
  • Re: join problem
    ... Create a new query in design mode. ... this looks like Employee & Artikeltype ... from query1 and SalesLastYear from query2. ...
    (microsoft.public.access.queries)
  • Extremely slow IN clause
    ... I have an updateable query, Query1, that includes a join between two tables: ... The ID field is the PK of Table2. ... returns over 4500 records in less than a second; adding the IN clause ...
    (microsoft.public.access.queries)
Loading