Hi Roxanne,

>>Please add that COUNT TO and SELECT CNT() work completely on the index if the expression is fully optimized and therefore usually require the index on DELETED() for all tables involved and that this index should only be removed for these tables, when the negative impact otherwise is stronger.
>
>So if I have no need for Deleted() tag, and SET DELETE is off, but my app commonly runs SQL SELECT COUNT(*)... statements, then I should have a deleted() tag on those tables involved in COUNT SQL operations?

OK, I'm guilty of not explaining it precise enough. :)

Count operations are performed by VFP when you issue the COUNT command, when have a SELECT statement with CNT() in the field list or HAVING clause or when it updates the value of _TALLY.

The filter criteria for these commands is the current SET DELETED setting, any SET FILTER in effect for xBase operations, the FOR clause of COUNT and the WHERE clause of the SELECT statement or any valid combination of them.

If this filter criteria is fully optimizable, VFP would only read the index tags that are needed to create the Rushmore bitmap and then counts the number of set bits in this bitmap. This is the number of records. Unless with any other operation, VFP doesn't read all records to verify that the criteria still applies to the current data in these records. In other words, it doesn't touch the DBF file.

As soon as the criteria is not or only partially optimizable, all records matching the optimizable criteria are read and tested against the non-optimizable parts of the filter criteria, just like any other database operation does it.

Hence, with SET DELETED OFF, you don't need an index on DELETED(), but you count deleted records as well. As soon as you add NOT DELETED() to the remaining filters, you need an index on DELETED(). With SET DELETED ON you always need a filter to speed up this operation. This means:

SET DELETED OFF
COUNT TO var

doesn't need an index, whereas the following do:

SET DELETED OFF
COUNT TO var FOR NOR DELETED()
SET DELETED ON
COUNT TO var

An index on DELETED() can speed up any count operation dramatically, especially if the index hasn't changed since the last access and the cached version can be used. Because its comparable fast it can be used as a pre-evaluation condition to let the user decide whether to retrieve a required subset or whether the user should re-fine it.

OTOH, an index on DELETED() can slow down many other operations, some of them significantly, if the number of deleted records is relatively small and the number of non-deleted records absolutely high, because in these cases the index must be read for almost any operation (unless the cached version can be used). And in some cases it can even produce wrong data, but that's a bug in VFP where we haven't found an easy repro-case yet.

Whether or not to use an index on DELETED() therefore depends on a variety of considerations. If you don't use count operations on a table, the default should be no index, unless tests show you that it is faster. If you have count operations, the default should be an index, unless tests show you that it makes other parts unacceptable slow.

Is that clearer?

Christof