I have to vouch for this. Having an index tag on DELETED() has greatly trimmed down our access time (from 1-15 secs to 3-4 secs, which the users now feel is acceptable).

It seems some are strongly against using the deleted() tag, but I'm still looking into documentation to back this up. Also, if you do have SET DELETED ON (which we do), then SYS(3054) will only report FULL optimization if this tag exists. That must count for something?

I'll be glad when we come to a conclusion to help us all out...


>>I forgot to mention that this only counts when you don't have considerable amounts of deleted records. If you don't, You won't have any benefit from the deleted tag. Make sure that you don't deal with filtered resultsets.
>>
>
>This is my point Walter. In your particular set of circumstances, you do not have deleted records. Be careful when preaching on a suggested general principles that are based on your particular set of circumstances. In word, it is a bit misleading. For you to say there is no benefit or that it is a burden in maintaining an index on deleted records is flat out incorrect.
>
>>You're quite right, But if the filter is on a column (or expression) with a very low selectivity (It returns very high percentage of the actual sourcetables) an index is not going to speed up performance very much. On the other hand, it could degrade performance when replacing, appending, reindexing data and the index could get corrupt. Then the index is more of burden than it helps.
>>
>
>The overhead of 1 additional index being a big burden? I don't think so. The size of the index is 1 byte per record. The deal is if you want FULL optimization, you need an index on deleted records. When it comes to large appends, I blow away the indexes and rebuild them after the fact. If you are looking for the BEST performance, that is the way to go. On incremental inserts, updates, etc, there is no measureable degradation.
>
>
>>In situations where you want to high speed appending data (for eg: An indexed SQL view) you'll find performance suffers a lot from the index. The statement I was trying to make (I agree i failed, but did refer to the discussion held a few months back) is that you should pick out your indexes carefully. If you don't have many deleted records in your tables an index is not going to help you speed up command unless:
>>
>
>Again, if you are doing large inserts, use a server-side COM component and build the indexes after the fact. That will give you the best performance. The presense or absense of a DELETED() tag is not going to make a difference.
>
>>Some will say: Hey why not adding the index, if it doesn't hurt ?
>>Well, In my experience it often hurts, when you'll get accidental filtered resultset which can lead to difficult trackable errors (like described earlier in this thread).
>>
>
>Use the NoFilter clause and you will avoid filtered results.
>
>>Besides this, I have a general rule: Only add indexes when you'll really need them. If a table contains only male persons you won't need an index on the gender column (unless you regular want know if there are female persons). IOW the selectivity of the index must be high.
>
>Here, you and I agree. The fact is, whenever you have Set Deleted On, if you have a tag on deleted records, Rushmore uses the index to aid in optimization. I have worked with too many LARGE datasets in SQL intensive envrironments to not come to any other conclusion.