>>In the example I posted to Jay, I have 2.5 million records. Here I need to check for D40CkNo being empty. I've run the following performance test.
>>
>>lnSeconds = Seconds()
>>Count For D40CkNo = ' ' To N1 && .845 2,497,224 records
>>?"1a1", Seconds() - lnSeconds
>>lnSeconds = Seconds()
>>Count For Empty( D40CkNo ) To N1 && .444 2,497,224 records
>>?"1a", Seconds() - lnSeconds
>>
>>In this case because the speed of the Empty( < field > ) is twice as fast.
>
>On first sight, I find this result weird. Do you have an index on deleted()? On empty(D40CkNo)? On the field D40CkNo? Do you have SET DELETED ON?
>
>OTOH, perhaps the difference is due simply to buffering: on the first COUNT, VFP retrieved records to its buffer, and accessed them faster on the second run. Repeating the test immediately should reveal what impact buffering has.
>
>BTW, I strongly suggest you repeat the tests, combining the condition with other conditions (at least, if you expect to use such a combination in a real-world situation). The result may be quite different.


Actually, I was surprised too. But then again I have one index that looks like this "Right( PadL( Upper( RTrim( Vin ) ), Len( Vin ) ), 6 )". I didn't realize that once the index is created the fetch on the records is still almost instantaneous.

I tested this a number of times. Originally, I had the two switched. Sometimes when FoxPro has to do a number of memory intensive commands it can actually slow things down. I realize this is more likely with a lot of disk activity which you are not getting with the COUNT. Each time the test showed that the one with the EMPTY index is approximately twice as fast. My boss thinks that it is because EMPTY is returning only a logical, while the index on the entire field has to return the entire string.

I don't know. I've read that having an index on DELETED() can be a mistake, as you suggested in your FAQ. Maybe if I have a small number of non empty D40CkNo instead of all empty this would change the results.