>Hi,
>I did some testing a couple of weeks ago checking all the VFP9 changes and their effects on SQL to see if any differences.
>The test dbf had 10 Million records, I randomly inserted 100 records thru it and deleted 100,000 records for the test.
>
>The following SQL executed in 0.05sec once I got it Fully optimized.

>
>

>SELECT RECNO() AS RP, * FROM invh WHERE ALLTRIM(BATCHNO)="2000" ORDER BY lastname, firstname INTO CURSOR aa nofilter
>

>
>You need a DELETED() index as well as the correct indexes so that rushmore can fully optimize.

I have a DELETED() index, and rushmore reports the query to be fully optimized.

>
>>>>>>>>If I were writing this thing from scratch, I'd do as you suggest, but unfortunately, with only God knows how many lines of code existing in this app, I don't have that luxury. Do you think it's safe to count on tricking VFP the way I'm doing it?
>>>>>>>
>>>>>>>No, it's not safe. Your results are not reliable.
>>>>>>
>>>>>>You know... I was afraid you'd say that. ;)
>>>>>>
>>>>>>For the record though, by 'not reliable', do you mean from run to run, or for future reference. So far, it's been completely reliable in my testing, but I don't know what MS might do later to screw it up for me and so far, I'm at a loss to find another way to speed this thing up. The way it is now, it's just waaaayyy too slow. Doing what I did took it down to sub 1 second. Well, I'm going to keep rummaging around for an answer, but in the meantime, I may have to treat this as a temporary fix.
>>>>>>
>>>>>You can not expect the same result in every test on every PC. How many tables do you use in your SELECT? Can you post it again because I jumped into the thread in the middle?
>>>>>
>>>>>Did you add NOFILTER clause at the end? Did it speed up the process? How many records are in the table and how many satisfy the filter criteria?
>>>>
>>>>That's going to be my next try. If it is as fast as what I'm doing now, I'll switch.
>>>
>>>It cannot be faster because NOFILTER turns off the exact behavior you've just found to be faster.
>>
>>Well, the lousy news is that it is far slower. It's interesting that if I just type the select into the command window after setting deleted on, it takes about 20 seconds to run. In the exe it takes about half that time. I checked the optimisatin with sys(3054,1) and it says the query is fully optimized. As long as deleted is on, it appears that no matter what I do, it's damned slow.