>Dmitry,
>
>A very dangerous action. In general if you don't have many deleted records. the DELETED() tag only slows things down. If you've got large tables accessed over a WAN, the DELETED() tag can slow your queries down to a crawl.
>
>The hackers guide is incomplete or plain misleading. If you've got performance problems, the please try to delete those tag and check if the situation has improved. In general there is no need at all to use them.
>

What Jon quoted comes from the VFP 6 Hacker's Guide. In the VFP 7 book (which was written after Chris Probst's article), we explained the situation. Here's what we wrote:

----Begin excerpt from HackFox 7----------

For many years (and in the earlier editions of this book), one of the mantras of optimization advice was to index every table on the DELETED() function, if an application was to run with SET DELETED ON. This advice was accepted by virtually everyone who knew anything about Rushmore.

Here's the way we explained it in the VFP 6 edition of this book:

"Even in many complex queries and FOR clauses, Rushmore performs its magic almost entirely on the relatively small and compact CDX file, a file structured with nodes, branches and leaves to be searched efficiently. When DELETED is ON, FoxPro has to check each and every record in a result set (whether from a query, a filter, or FOR) to see if it's deleted—even if no records are actually deleted. This sequential reading of the entire cursor or file completely defeats the benefits of Rushmore. Don't do it!

"By creating a tag on DELETED(), you let Rushmore do the checking instead of looking at each record sequentially, which makes the whole thing much faster. The larger the result set, the more speed-up you'll see."

Sounds really good, doesn't it?

The walls came crashing down on this piece of wisdom with an article by Chris Probst in the May '99 issue of FoxPro Advisor. Probst was working with an extremely large data set in a network environment and found that some of the queries were just too slow. Monitoring network traffic found the problem: The portion of the index file related to the DELETED() tag was huge (at least in some cases), and transferring it across the network was bogging down the whole query.

Probst's experiments determined that, when an expression has only a few discrete values (like .T. and .F.), and the values are unevenly distributed (as is typically the case with DELETED(), since in general, few records are deleted), queries run faster without a tag on the expression. In those cases, it's better to let VFP narrow things down first based on the other expressions involved, and then do a sequential check of the remaining records.

The phenomenon Chris witnessed is not unheard of in the database world. The index with few values is called one with "low selectivity" and is considered suspect in most database designs. But this was the first documented case of the low-selectivity issue appearing in FoxPro's remarkable optimization.

What does all this boil down to for you? For small tables in a desktop or LAN situation, we generally think having a tag on DELETED() is a better choice. As tables get larger or when operating in a WAN situation, skip it. And just for good measure, you should test the performance, in your specific production (as opposed to development) environment, with and without the DELETED() tag.

----End excerpt from HackFox 7----------