Nancy,

Perhaps there was a small misunderstanding here. I was not talking about filtered indices (example: index on clientcode for not deleted()", but about indices whose expression was "deleted()"; example: index on deleted() tag deleted. Some users (myself included) used to create this index for all tables, in the erroneous belief that this would increase optimization - it does, but only in the sense of "complete Rushmore Optimization", which, as we have seen, is not always the most efficient thing.

Example:

set deleted on
select ... for ClientCode = "123"

The expression which VFP really evaluates is: for ClientCode = "123" and not deleted(). If there is an index on deleted(), VFP will use it - whether it makes sense or not. In this case, it doesn't: probably, when evaluating the expression deleted(), more than 90% of the keys will have to be fetched from the index. This is slow, especially over a network.


One more point, which I had forgotten: you can sometimes speed things up with composite indices.

Instead of:

select ... for ClientCode = "123  " and Country = "Bolivia  "
* Indices exist on ClientCode and Country

You might use an expression like:

select ... for ClientCode + Country = "123  Bolivia  "
* An index exists on ClientCode + Country

In some cases, only a single record will be fetched directly from the index! This is much faster, for VFP, than getting, say, 30 records for the first part of the expression, 50 records for the second part, and then building and ANDing the bitmap!

Creating the additional index is only worthwhile if you frequently access data on one specific set of criteria.


And yes, I use surrogate keys almost exclusively. If a filter exists on an index, the index will not be used for Rushmore Optimization - this may be good, or bad, as we have already seen. Candidate indices (ClientCode, InvoiceNumber) must still be filtered; if you need an index for Rushmore Optimization, you may want to have two: one (filtered) for checking uniqueness, another (non-filtered) for Rushmore Optimization.

Regards, Hilmar.

>Hilmar-
>
>
>>Remember, too, *not* to include any index on the expression deleted(); this will also return too many records.
>
>I really enjoyed your very lucid discussion of optimization. Just a small quibble. If people have a filter on DELETED() on the PK index, then they should have an index on Deleted(). Which can cause the problems you point out.
>
>All the more reason to use surrogate keys...one doesn't need the deleted() filter.