>Here is a recent discovery. By adding a tag such as:
>
>

index on deleted() tag deleted

>
>This causes our SYS(3054,11) to report that it is optimizing on the deleted tag and that the optimization level is Full for the first pass. Intermediate joins are now being reported as partial which we believe is due to a intermediate table being joined to an optimized table.
>
>As it turns out, the intermediate join (table 3 to the resulting join of tables 1 and 2) was Partial due to a filter condition placed on table 3 that was not optimized. By adding an index to table 3 that matched the filter expression, the optimization reported full.
>
>Thus, we are left with the following lessons in Rushmore optimization for SQL.
>
>1. Include an index tag called "Deleted" whose expression is "DELETED()" on all tables involved in a SQL statement.
>
>2. Set SET DELETED to ON.
>
>3. Ensure that WHERE clause Filter Conditions match perfectly to properly optimized index tags.
>
>In our experience we saw Rushmore take a 8 to 12 second query and perform the same work in about 1.4 seconds. What this means to our products overall is a performance jump for our clients (the data sets are huge).
>
>Additionally, since our products are data warehouse in nature (i.e. no user data is added or deleted in the delivered product), we have come up with the following rules for the delivered product:
>
>1. We do not ship indexes with the shipped data. We create the indexes at installation time.
>2. We are rewriting the SQL Query Builder portion of the product to pay attention to optimization and create indexes on the fly that match the queries of the end user.
>
>Thanks for being a sounding board. We appreciate it.
>
>Larry Rix

Larry,

While having an index on DELETED() will cause SYS(3054) to report full optimization, your query can actually run SLOWER than without the DELETED() tag. The reason is that Visual FoxPro indexes are organized as balanced-trees. A balanced-tree is a great data structure for quickly sorting and searching through data that has a large domain. Character strings and integers are great candidates for indexes for this reason. Conversely, a balanced-tree becomes no better than an ordered-list when the data only covers a small domain (such as the deleted flag).

This means that while VFP reports full optimization, there are cases when partial optimization is faster because VFP does spend CPU time loading and traversing the nearly useless index on DELETED().

There are a bunch of variables that determine which method will be faster. Luckily, we don't need to know why, only which one. I suggest running benchmarks on both methods with your database to see what works better in your situation.

- Keith