In a reaction to the discussion whether or not to use an index on DELETED(), i want to say the following


- Indexes with a tag on deleted don't improve performance when there are not many DELETED records.

When there are not many deleted records in the desired recordset, the optimizer won't improve the performance as it must use an extra index which could be more of a burden than a benefit.

- Partial optimization could be better than full optimazation.

Many of use are mislead by thinking that full optimization is better and faster than partial optimization. This is simply NOT True in all cases:
If 95% of the resultset is optimized by a small index and only 5% by a large index (such as an index on a C(100) field) it could be wise to drop the large index as it slows down performance when updating or inserting large amounts of data.

This rule applies to the deleted tag as well. If a resultset won't have much benefit of the deleted() tag it may run even faster without it.

- By working with very large tables a tag on deleted could be dangerous.

Very large tables means very large indexes. To let the optimizer to do its work the indexes must be in memory. When there is not sufficient memory to load all the indexes the optimizer won't work at all resulting in a dramatic increased response time.

- Large tables never should have many deleted records.
as they take up a lot of diskspace which in itself decreases performance.

- Indexes on DELETED() should only be used in some particular cases.

In some multitable query's or Relations the performance could benifit by a index on deleted(). If tables contain more than a few deleted records and are joined with eachother in a Query, the performance COULD be tuned with a index on DELETED().

IN GENERAL: Don't use index tags on DELETED()


just my fl. 0,02

Walter,