Larry,

I predict that you will hear from several people on this message.

For about 2 years, now, there has been on-going discussion here about the real effect of a TAG on DELETED() with SQL in the case where you are running with SET DELETED ON.

The consensus is that it is only in the very RARE case that it is useful despite SYS(3054) reporting 'partial' without it!

Now your may well be one of those rare cases, but you certainly should NOT apply the logic you have arrived at blindly.

SYS(3054) reports "full" with the index on DELETED() and you are running with SET DELETED ON, BUT. . .Rushmore will now have to bring that whole index (on DELETED()) down to the workstation before it can successfully process the query.
This is rarely beneficial, especially in the case where there are no or only a small percentage of records, that actually are deleted.

Good luck with this. Your fun is just beginning.

Jim Nelson


>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