Walter,

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

Your methodology is way too pegged on specific circumstances. Having a tag on deleted records will allow optimiziation in any circumstance. I understand your situation where you don't have many deleted records. However, in other situations, that may not always be the case. It's all about building in flexibility. If you have a limited set of circumstances, you can definitely trim the rules to fit your needs. My point here is that you are knocking a set of GENERAL guidelines based on your SPECIFIC and LIMITED circumstances. It is like comparing apples and oranges. The fact is, there are many apps that can have a decent amount of deleted records. And if I don't have deleted records, there is no discernable performance degradation with the additonal index. Unless of course, you are working pathetically low-end equipment. In that case, the issue rests with hardware, not VFP itself.


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

You see again, you are pegging your argument on the premise that NO deleted or a modest amount of deleted records. Your assertion here is not portable against the vast array of possbibilities.

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

Have you seen close to all the VFP apps running today? If you know me, you know not to throw quantitivate data at me when it is clear that it is really your opinion, your best quess. You don't really know what most VFP apps out there consist of with respect to deleted records. Please don't put stats out there that you have no way of verifying. At the very least, you should disclaim those assertions.

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

Yes, partial optimization in many cases will get you all of the way there. However, there is a reason why the concept of full optimization exists < s >.

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

No, it is far more effective to blow the indexes way, do the large append, pack if necessary, and then rebuild the indexes after the fact. If large appends are occuring, that should not occur during production hours anyway.

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

2.what?? I am happy to say that I have tossed every manual dealing with Fox 2.x and have blown away Fox 2.x off my system. Bottom line, even in a 2.x system, you can run the maintenance functions on the server.

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

You know where the problems will occur through testing....

As a general rule, what you are suggesting is bad advice. It smacks in the face of generally accepted principles. Those principle have proven success. To suggest not using a deleted tag as a general rule is about as off the wall as Jim Nelson's article on why naming conventions are not good.

We'll agree to disagree here. The overwhelming body of evidence however, is in my favor..