Indexing on ALLTRIM(batchno) is illegal or at least incorrect. If the length of batchno is not fixed, it will give different index keys, which is illegal and may cause fatal errors. If the length of batchno is fixed, then alltrim() is redundant. My rule of thumb is "avoid alltrim() unless absolutely necessary".
>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.
>
>>>>>>>>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.
