Hi Nadya,

I posted this to Mike but also wanted you to get 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--------

>What sys(3054,11) would return?
>
>>Thanks for the reply.
>>
>>However, it's not a solution to this particular query that I'm after. I know how to optimize it.
>>
>>I thought that SELECT ... WHERE (field) IN (subquery) was optimized.
>>
>>My findings from two queries below would indicate that this SQL structure is NOT optimized in VFP.
>>
>>Can ANYONE CONFIRM if this is true?
>>
>>Thanks!
>>
>>Shawn--------
>>
>>
>>>>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
>>>
>>>Here is what should give you the same result:
>>>

>>>>  SELECT gen.file_id ;
>>>>  FROM datatrac!gen gen  ;
>>>>  WHERE gen.trade_no = 0 .AND. (gen.app_date >= DATE()-30) ;
>>>>  INTO CURSOR tmp_cur1 NOFILTER
>>>   index on file_id tag file_ID
>>>
>>>
>>>>  SELECT gen.int_rate ;
>>>>  FROM gen ;
>>>>  INNER JOIN tmp_cur1 on gen.File_ID=tmp_cur1.File_ID ;
>>>   INTO CURSOR tmp_cur2

>>>
>>>BTW, you can check _tally after first select and not execute it at all, if the first returns 0 records.