Doug,

Actually, every thing about that original SQL statement was "optimizable". Trouble was, doing a six way join overloaded VFP's ability to optimize anything. It was being forced to create massive temporary tables. Breaking the single SQL statement down into "bite sized chunks" allowed VFP to accomplish everything in memory. (having 1/2 GIG memory didn't hurt!)

Dragan was on to something when he suggested slicing off just the key values your query needs into a cursor or temporary table.


>I think you just learned what "optimizable expressions" are. Welcome to Visual and goodbye to brute force coding!
>
>*g*
>
>
>>I think it was George Goley that once said, "a smart programmer can always beat Rushmore".
>>
>>You know more about your data than VFP & Rushmore ever can. I had an experience several months ago where I was attempting to create a dozen tables all involving fairly complex calculations and a six way join. After running the first table, I realized it would take 6 hours to complete. (biggest source table was over 8 million records). Needing to get home to dinner, I decided to break the process down into three separate steps:
>>
>>1. creating interim tables with only key values (via SQL select);
>>2. set relation and copy data to secondary temp tables; and
>>3. create final results from secondary tables (via SQL select).
>>
>>I figured I would cut the processing time from 6 hours down to 20-40 minutes. I was amazed to discover that the whole process ran in 3 seconds! At first, I thought I had done something wrong. But no, the Fox really is that fast when you help it along.
>>
>>>If someone had similar experience, I'd like to shed some light on the issue. What I'd like to know is whether this is a general rule, or it has its ups and downs, or was I just plain lucky in these cases.