>>>
>>>I can speed things up from anywhere from 10 to 20 seconds down to sub 1 second if I can use this trick. But if this behaviour is likely to change in order to standardise along ANSI lines, then I need to find another way to do it, and nothing else I've tried has really worked well so far.
>>>BTW the table does have an index tag for DELETED().
>>
>>Approximately how many records do you usually select?
>
>Depends on which query is running. Might only be 1 record, might be 10 records. Probably not more than 10 or so right now. As their use of the system grows, that number could rise dramatically.
Since you use VFP9, I suggest that you take the advantage of the binary index which is extremely much faster than a normal index, especially for expressions like deleted(). That is, in stead of
Index on deleted tag del, you use
Index on deleted() tag del binary.
I also suggest that you stick to
Set deleted off
Select whatever
Set deleted on
But I also suggest that you add the Active field I mentioned in another message, and change your code to use that field in stead of deleted() over time.