Hey Tamar,

Enjoying FP Advisor articles. Good stuff.

I ran the query exactly as you had it below. The time reported by Fox was 166 seconds for 171K records. (Not sure where I got 20 minutes--may have been on similar Select with other tables.) Still too long for results. I believe SCAN-ENDSCAN or even a COPY TO would be faster using SEEK() or FOUND(). The SYS(3054) reported no optimization level on tables (no WHERE clause filtering, so that's understandable) and it is joining tables on index tag CustSerID. Custser is 900K records and Contlist is 741K records. If I can filter on other conditions, it is much faster, to be sure.

The problem as I see it is the legacy code. There may be hundreds of programs that presently run correctly but either might not run or would return incorrect results because of changes in the SQL engine. Do you know what conditions changed in the SQL Engine or where I can find that nformation? I want to migrate code to VFP8 (and 9 later this year), but this issue would halt that dead in its tracks.

Thanks,

Tim

>>SELECT cs.* FROM CustSer cs LEFT JOIN Contlist cl ;
>> ON cs.CustSerID = cl.CustSerID ;
>> WHERE cs.CustSerID # cl.CustSerID
>>
>>I realize I could use a sub-select query, but the time it takes to run is unacceptable (20 minutes).
>
>It's hard to imagine why the following would be slow:
>
>SELECT cs.* FROM CustSer cs ;
> WHERE cs.CustSerID NOT IN ( ;
> SELECT CustSerID FROM Contlist)
>
>Have you tried running this with SYS(3054, 11) to see what's optimized and what's not?
>
>Tamar