C
Carl Rapson
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
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