Hi sergey,

>When SET DELETE is ON VFP looks for index on DELETED() to optimize query. If you don't have such index VFP will show partial optimization. Addidng index on DELETED() may or may not speed up your query. To verify that missing index on DELETED() is responsible for partial optimization you can temporary SET DELETE OFF and rerun your query.

I think it is very important to note that adding an index on DELETE() should not be done unless you're doing this for VERY specific reasons. Adding a DELETE() index might have little performance advantage when doing queries locally or portions of the tables are buffered on the workstation at the OS level.

Working with an INDEX on delete in a network environment almost is always degrading performance in stead of improving, especially when the table is getting larger and larger. I've identified cases where a table of a few million records took about 3 seconds to only open.

People who are doing performance tests and tuning with and without an INDEX on DELETED() almost always are doing a very poor job. The most common mistakes:

1. Fully optimizable is almost never the fastest query, just pick you indexes carefully. Most of the time a partial optimizable query is the fastest. When doing a xBase commands or SQL select on very small tables. NON optimizable might be the fastest.
2. They test on their local workstation which is not representative for a network app.
3. If the test on a network, they don't take into account that parts of tables can be cached on a local workstation (Opportuistic locking) not giving reliable results for an app in production. In stead make sure that the tables to query are opened at other workstations also and have written same data to it to let the (N)OS disable the opportunistic locking.
4. Don't do a simple SELECT * FROM ... WHERE ... INTO CURSOR x type of query, because VFP might create a filtered cursor instead of a new fres cursor. The results simply don't compare.
5. Don't claim a performance difference of 50% or more when the absolute difference is smaller than a few tents of a second. It is not reliable for a production app. Esspecially when tables grow larger the advantage very soon could be a massive disadvantage.



Further, if a table does not contain massive ammount of records the performance difference are always neglible. In general it simply does NOT make sense to index in deleted() unless:

1. You want to optimize the count command. This command when fully optimized does not touch the orignal table but simply counts the nodes in the indexes. Therefore a fully optimizable COUNT statements could be 1000's of times faster than a non optimized one. But be carefull, using an index on deleted might cause serious performance degration on larger tables.

Walter,










>>Hello all,
>>
>>Could someone please explain something to me?
>>If I have a local view on to a single table that just selects some output fields and filters on an ID value being equal to a variable. If my table is indexed on the ID why does my optimisation come back saying that it is using the index but that it is only partially optimised?

>>SELECT *;
>> FROM ash!agent;
>> WHERE Agent.iid = ?ThisForm.ID
>>
>>
>>Using index tag Id to rushmore optimize table agent
>>Rushmore optimization level for table agent: partial

>>
>>And does this matter?
>>
>>Many thanks
>>