>Walter,

> I think the key is what is the highest % of deleted records that could be in your table. I assume with SET DELETED ON that VFP would have to omit ANY deleted records from a result query without optimization. This may not be a huge issue with a few dozen records in a 5000 record table. But in a 20 million record table with tens of thousands of deleted records then not having the DELETED tag could mean you are losing lots of optimization. If 50% or more of the records in the file could be deleted it would be hard to argue against including an index tag to improve performance.

It depends.. First off all you should keep the number of deleted record to a minimum. Second, you should use a strategy to avoid deleted records to fall in the range of rushmore optimizable queries. For example if all invoices before jan 1st 2002 are deleted, there is an index on invoice date, and you are querying for invoices later than 2001, you don't need a index on deleted because the index on invoice date is already excluding the deleted records. However if you've got an index on deleted the index is dragged into rushmore and slowing down your query significantly.

Indexing a 20 million record table on deleted() seems not to be a good idea in a network environment. It slows down rushmore optimizable queries to a crawl, since for every query millions of indexnodes are forced into the optimization process.

>I'm assuming that is why AccPac includes the DELETED() tag for practically every table. In bill of material applications where steps and processes are constantly deleted and shuffled around it's very easy for 10%-20% of records to end up deleted. Of course since you have to kick out every user of the system to pack the files, the tables can go months without packing unless you have a system administrator that makes a point to do it.

I assume that they use a DELETED() Tag because of an old myth. There are numerous ways of avoiding the issue.

>One accounting manager I work with never allows any packing of files EVER! She wants the deleted records for an audit trail. While I have argued with her on this point there have been several occassions where we have gone back to the tables months later and found deleted records that answer questions as to why a particular event happened.

This makes a case for loggin deleted records in another way, if you'd ask me.

Walter,