Some times ago, I've done some tests based on what I've read in FAQ section about Rushmore. Seems that, if the table has many records , query execution time over the network depends on many factors (SET DELETED, index on DELETED(), indexes with few distinct values, etc.).
The point is that every one must test queries over the network and choose the right way.

Agree with network read caching and oportunistic locking. Those settings can lead to table corruption as well.

Have a nice day.

Dorin

>Hi Dorin,
>
>>Does make a difference. You can check using SYS(3054) function. If SET DELETED is ON and there is no index tag on DELETED(), queries are only partial optimizable. Test queries against a 500k-1000k records table (network, not locally) and you will see a big difference in execution time. If you have an index on DELETED(), execution time can be inferior even if the query is fully optimizable .
>
>I'm not sure what you're trying to say here. However, sys(3054) does only display index optimization levels not a cost base optimization level. Whether a query is fully or partial optimizable in fact does not say anything about the most efficient way to execute a query. In fact when using a very selective index (also called discriminating index) any additional less selective index might only be a burden as the optimizer has to process the index information while it does not lead to any performance advantage at all, but only adds overhead, degrading performance.
>
>People are confused by the effictiveness of such strategy because:
>1. They don't use a NOFILTER clause in the SQL - SELECT command, in wich in the case of a simple fully optimizable query only creates a filtered resultset, leaving the programmer thinking they've hugely optimized the query for performance.
>
>2. They test on a local machine, where the bandwidth between a harddisk, RAM and the CPU is huge. Kick in a network and you'll see different results.
>
>3. When testing over a network, be sure that there is no (read)caching on the OS level involved. You can do this by opening this table on another workstation and write some data to it. The NOS will disable read chaching for all workstation of those table using this table and therefore will produce a far better real world situation. (Exclusive opportunistic locking mechanism, see MSDN).
>
>
>Bottom line is that all tricks with INDEX on deleted(), SET DELETE ON/OFF, Full optimization, etc. DON'T WORK AT ALL in a real world situation. Just use SET DELETED ON and make sure you don't have huge amounts of deleted records in there and have a few good indexes in place.
>
>Walter,
>