Re: Extremely slow IN clause

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <cr@xxxxxxxxxxx> wrote in message
news:%23Zv6xEbRFHA.508@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uB%236nhSRFHA.3312@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>
>


.

Relevant Pages

  • Re: Extremely slow IN clause
    ... How much time it takes to run the Query2? ... time for each possible value of Query 1). ... the only way I could find to incorporate Query2 into Query1 is to use ... > returns over 4500 records in less than a second; adding the IN clause ...
    (microsoft.public.access.queries)
  • Re: Creating query without using Wizard
    ... You should just have one query w/ a better WHERE clause ... Here's a better query (but w/o the required 3rd significant ... rely on query1 or query2 ...
    (microsoft.public.access.queries)
  • Re: Select Top 5
    ... when looking at a record in query2 ... xbox Bill Gates $465.00 ... office software Bill Gates $200.00 ... The query that I ...
    (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: 1st, 2nd, 3rd, etc
    ... second highest 2nd ... I might make a query that groups on the score. ... add Query2 to the builder and link Score To ... A purely SQL query will run faster but requires a bit of SQL knowledge to write. ...
    (comp.databases.ms-access)