Extremely slow IN clause

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
 
S

Sylvain Lafontaine

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.
 
C

Carl Rapson

Sylvain,

Thanks for responding. Query1 and Query2 alone both run in about 1 second.
Here's the scenario...

I have a subform whose RecordSource is Query1. In my parent form, I'm
modifying the subform as follows: build a filter string containing the IN
clause as well as some other conditions. My filter string winds up looking
something like:

filterstr = "Table2.ID IN (SELECT ID FROM Query2) AND Field1='value1' AND
Field2='value2'"

I then apply this filter to the subform:

subform.Form.Filter = filterstr
subform.Form.FilterOn = True
subform.Form.Requery

It's at this point the query takes a long time to finish. If I comment out
the IN clause, the subform loads quickly (less than 5 seconds). I don't
think Query2 is running multiple times, but how can I tell for sure? Even
running Query1 in a query window with the IN clause added takes an extremely
long time.

I'll give some thought to using a temporary table.

Thanks for your assistance,

Carl Rapson
 
S

Sylvain Lafontaine

What's the code for Query2?

Also, have you created a query object for Query2 or if you write its entire
code in filterstr?

Finally, by using parameters directly in Query2, it is possible that you
will get some speed up.

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


Carl Rapson said:
Sylvain,

Thanks for responding. Query1 and Query2 alone both run in about 1 second.
Here's the scenario...

I have a subform whose RecordSource is Query1. In my parent form, I'm
modifying the subform as follows: build a filter string containing the IN
clause as well as some other conditions. My filter string winds up looking
something like:

filterstr = "Table2.ID IN (SELECT ID FROM Query2) AND Field1='value1' AND
Field2='value2'"

I then apply this filter to the subform:

subform.Form.Filter = filterstr
subform.Form.FilterOn = True
subform.Form.Requery

It's at this point the query takes a long time to finish. If I comment out
the IN clause, the subform loads quickly (less than 5 seconds). I don't
think Query2 is running multiple times, but how can I tell for sure? Even
running Query1 in a query window with the IN clause added takes an
extremely long time.

I'll give some thought to using a temporary table.

Thanks for your assistance,

Carl Rapson
 
V

Van T. Dinh

I *think* the problem may be in the qualifier "Table2" you use in the Filter
String. You are filtering the Form's RecordSource, not the Table.

Get rid of "Table2" in "Table2.ID" and try to see if it makes any difference
....
 
C

Carl Rapson

Sylvain,

Query2 is a query object, not created in code. I refer to Query2 in the
filter string:

....WHERE ID IN (SELECT ID FROM [Query2])...

Query2 is built as follows:

Base Query:
SELECT DISTINCT Field1,Field2,Field3,MAX(Field4) AS Latest
FROM Table2
GROUP BY Field1,Field2,Field3;

Query2:
SELECT Field1,Field2,Field3,Latest,Table2.ID,Table2.[Other Fields]
FROM [Base Query] LEFT JOIN [Table2]
ON ([Base Query].Field1=Table2.Field1)
AND ([Base Query].Field2=Table2.Field2)
AND ([Base Query].Field3=Table2.Field3)
AND ([Base Query].Latest=Table2.Field4)

So Query2 returns only records from Table2 that have Field4=MAX(Field4).

BTW, I tried your earlier suggestion about using a temporary table, and that
speeds things up considerably. Before I build my filter string, I populate
the temporary table with the ID values from Query2, then I use the temporary
table in my filter string (WHERE ID IN (SELECT ID FROM temptable)). So it
appears it's not the IN clause per se, but something about using Query2 in
the IN clause that is so slow. I'm not completely happy with this way of
doing it, but if I can't figure out how to speed up the IN clause I will
probably continue to use it.

Thanks again,

Carl
 
S

Sylvain Lafontaine

The use of a LEFT JOIN instead of an INNER JOIN in Query2 looks suspect to
me. Also, if you need only the ID value, then selecting the fields Field1,
Field2, Field3, Latest and Table2.[Other Fields] in Query2 are useless.
Most of the time, you shoud only select the required field(s), even if that
means writing another query.

However, this doesn't explain the big change in speed. Maybe a look at the
query plan used by Access will give you some insight on this:
http://builder.com.com/5100-6388_14-5064388.html

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


Carl Rapson said:
Sylvain,

Query2 is a query object, not created in code. I refer to Query2 in the
filter string:

...WHERE ID IN (SELECT ID FROM [Query2])...

Query2 is built as follows:

Base Query:
SELECT DISTINCT Field1,Field2,Field3,MAX(Field4) AS Latest
FROM Table2
GROUP BY Field1,Field2,Field3;

Query2:
SELECT Field1,Field2,Field3,Latest,Table2.ID,Table2.[Other Fields]
FROM [Base Query] LEFT JOIN [Table2]
ON ([Base Query].Field1=Table2.Field1)
AND ([Base Query].Field2=Table2.Field2)
AND ([Base Query].Field3=Table2.Field3)
AND ([Base Query].Latest=Table2.Field4)

So Query2 returns only records from Table2 that have Field4=MAX(Field4).

BTW, I tried your earlier suggestion about using a temporary table, and
that speeds things up considerably. Before I build my filter string, I
populate the temporary table with the ID values from Query2, then I use
the temporary table in my filter string (WHERE ID IN (SELECT ID FROM
temptable)). So it appears it's not the IN clause per se, but something
about using Query2 in the IN clause that is so slow. I'm not completely
happy with this way of doing it, but if I can't figure out how to speed up
the IN clause I will probably continue to use it.

Thanks again,

Carl
 
C

Carl Rapson

Sylvain,

I'll look at the LEFT JOIN and see if I can change it to an INNER JOIN. Also
I'll look at the link you provided. Thanks for all your assistance.

Carl Rapson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top