Hi Mike,

Thanks for the reply.

I was unsuccessful at optimizing the query using SELECT ... WHERE (field) IN (subquery).

But I did manage to return the same resultset with optimization using an INNER JOIN like so:

** QUERY #1: **

SELECT gen.file_id ;
FROM gen ;
WHERE gen.app_date >= {03/01/2001} ;
INTO CURSOR tmp_cur1 NOFILTER

** QUERY #2: **
SELECT gen.int_rate ;
FROM gen ;
INNER JOIN tmp_cur1 ON tmp_cur1.file_id = gen.file_id;
INTO CURSOR tmp_cur2

Interesting... I won't trust WHERE (field) IN (subquery) anymore.

Thanks,
Shawn--------


>I performed a similar query against tables with indexes. I asked for a listing of all people who have entries in a table listing people with contracts.
>
>The query was SELECT * from people where ppl_pk in (select bpc_pplfk from buyerspercontract)
>
>Since there are no limiting conditions on people or buyerspercontract, sys(3054,11) indicates no optimization on these, but did indicate a join between people and buyerspercontract using the tag bpc_pplfk. There are 17553 records in buyerspercontract and 21696 records in people. Results were 17553 records in .36 seconds.
>
>select * from people where ppl_pk in (select bpc_pplfk from buyerspercontract where bpc_primary#{}) into cursor temp
>applies a filter to the subselect and it indicated partial optimization on buyerspercontract (as expected) but still pulled the now 6918 records in .34 seconds. So I'd say the command is pretty much optimized.
>
>In your case, your subselect is going against an unindexed datasource (tmp_cur1) so I'd expect no optimization. Can you not do something like this?
>SELECT int_rate ;
> FROM gen ;
> WHERE file_id IN (SELECT file_id FROM gen where trade_no=0 and app_date >= date()-30) INTO CURSOR tmp_cur2
>
>>Hi Everyone,
>>
>>This query returns no records:
>>
>> SELECT gen.file_id ;
>> FROM datatrac!gen gen ;
>> WHERE gen.trade_no = 0 .AND. (gen.app_date >= DATE()-30) ;
>> INTO CURSOR tmp_cur1 NOFILTER
>>
>>This query takes 50 seconds to execute:
>>
>> SELECT gen.int_rate ;
>> FROM gen ;
>> WHERE gen.file_id IN (SELECT file_id FROM tmp_cur1) INTO CURSOR tmp_cur2
>>
>>Because the first query has NO records and because it's taking 50 seconds, my assumption is that the 2nd query is not optimized. Does anybody know if SELECT ... IN (subquery) is unoptimized IN VFP 6.0?
>>
>>Please help if you can. Thank you. Good to see all of you at the DevCon. Need to update my picture. I think that's from highschool! :)
>>
>>Shawn Nelson