John,

>This is my point Walter. In your particular set of circumstances, you do not have deleted records.

Not quite right. I did say: Not MANY deleted records. If you have about 5% of deleted records in the optimizable resultset of a SQL select query, you won't experience an significant performance improvement by a DELETED() tag. (I did examine that the performance gain for 50% deleted records was about 60% or 70%, set the thread held a few months back. I can't remember the name exactly. search for DELETED(), for 5% of deleted records the gain would be about 3% or less)

If you take one of your projects, delete all deleted() tags and run it you'll find no significant performance gain when:

- You don't use COUNT for FULLYOptimizableExpression
- you don't use filtered resultsets made by a SQL-SELECT Statement.
- You don't have tables with lots of deleted withing the optimizable resultset withing the SQL statement.

When you carefully look at these three conditions, And take these guidelines with your design of the system, you would seldom need a tag on deleted().

>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.

In your words, you were preaching the opposite, which is also missleading (and in my opinion even more missleading). And if you look close to all the VFP applications running today, you will find that most of them will not have many (less than 5% of deleted records) in their tables. Deleted records are almost always unwanted and therefore should not exist. Because we don't have a gentle way in VFP to get rid of the deleted records, we have to live with them until we have a chance to pack the table or find a way to recycle the records.

>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.

But that is just the point. Full optimization doesn't have to mean, that it is faster than partial optimized queries, especially if an particular index within the query does not filter out significant amounts of records (like the deleted tag).

>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 this way, you'll have to have exclusive access to the table, which is not always available. If it is, It's far more effective to pack the table.

>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.

This solution stinks. Comm servers cannot be created for FP 2.x versions, You'll have to have exclusive rights to the table and recreating indexes take up time as well.

>>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.

Well, thats nice if you know where the problems can occur. If you already have a project running and then add the deleted() tag, you're praying for problems.

>>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.

Yep, that's right, but full optimization doesn't say its faster than a partial optimized query. Thats the point, many of us here think that reaching full optimization is the goal, but it isn't. There is an article written in Fox advisor regarding this subject.

>I have worked with too many LARGE datasets in SQL intensive envrironments to not come to any other conclusion.

I too have worked with very large tables, and always tried to keep the number of deleted records low or filter the deleted records out by an other index (like invoicedate if the invoicerecords of last year are deleted). In all cases i did not experienced significant performance gains (less than 1%) by adding an DELETED() tag.

Generally, I would say: Don't use the deleted tag, unless you've particular circumstance where you really need them. If you look arround you, you'll come to the conclusion that these are very rare circumstances.

Walter,