Will and Ko,

You've got to be very carefull using low selective indexes. By this I mean that a cretain indexed field contains only a few different values. For example all logical fields are low selective: it only contains the values True, False and NULL (if applicable).

Primary and candidate indexes OTOH are high selective: a certain indexvalue applies to one record only, so each record has a different value.

The deleted() index does not add any significant contribution to the selection process. You might have some (insignificant) performance gains by using it on a local workstation. But on a network it generally only slows things down on larger tables. When doing a query with SET DELETE ON it drags the whole index tag from the network to the workstation, while rushmore can only use it to filter out deleted records. The time needed to drag the index to the workstations memory depend on the networkbandwidth and the number of non-deleted records (the indexnodes of non deleted records are dragged). As the number of records grows the more performance problems you'll get.

In this situation generally the time needed to drag the indextag trough the network takes a lot more time than to filter out deleted() records in a non optimized way.

You can check this in the following way. Take a large table (more than million records), preferrable through a network, have a primary index and an index on deleted(), then do a:

SELECT * FROM LargeTable WHERE Pk=anypkvalue INTO CURSOR mycursor NOFILTER

and not the time, needed to execute the query. Now remove the index tag on DELETED() and do this query again. As the number of records grows the difference might be more than 1000 times.

The idea behind this is that querying and processing index information does also take time. Indexes with a low selectivity (like he deleted() tag, but also indexes on fields like sex, discontinued, status) might actually burden performance more than it helps. Using high selective indexes only in you query my tune your performance. In fact in some tables you might be better of to remove all rarely used and low selective indexes to give rushmore a real performance boost.


Walter,





>Hi Walter,
>
>I think we need to get our acts together here! ;-)
>
>>Are there any deleted records in your tables? if so then consider a Deleted() >tag.
>
>>Are you using DELETED() index tags on your table. Try to remove them. They're >notorious for slowing things down, especially in a network environment.
>
>What is the general advice on Deleted tags? I thought they made queries faster if there's more than x% deleted records.
>
>Will