Hi Walter,

LOCATE,SCAN,SET FILTER,SUM,CALCULATE,... are all not optimised.

>Hi fabio,
>
>That indeed is strange that the COUNT() command seems not to be optimizabel this way.
>

If next it is true, VFP9 need a like SET OPTIMIZE TO 90 new command for disable it.

>There is another, but far bigger problem with this FOR !DELETED() optimizable index expression.
>for any query done with SET DELETE ON, it is going to drag down the whole index with this filter and uses this in a bitmap.
>
>if a table with an INDEX ON Item FOR NOT DELETED() and I've got a query like
>
>

SELECT * FROM Items WHERE Item = "Bike"

>
>I would expect, it would only download the indexnodes matching "Bike". But my tests show otherwise. It downloads the indexnode matching "Bike", Downloads the entire index, creates a bitmap, process the join and gets the result. IOW it operates in very much the same way as the old INDEX ON DELETED()
>
>The result is that such a query on a lengthy table with uncached data is far and far slower than without the filter. From the limited testing I've done, it is even much worse as using the INDEX ON DELETED() tag.
>
>Even worse is that all VFP programmers using FOR !DELETE() clauses in their existing applications to handle the uniqueness of intelligent keys with upgrading will see that their application will perform worse because each and every query on that table is downloading the whole tag for DELETED() optimization regardless of the field beeing reference in the query. And there is no documentation telling them that (it is not in the VFP9 helpfile from the public beta). In my eyes this optimizable filter is complete BOGUS and does show that the VFP team itself does not have a clue what they are doing here.
>
>The binary index enhancement is not useless in itself as it truly makes optmizing boolean fields less resource consuming. But in the end you'll have to question if it is worthwile to index low selective fields. In general this is not the case, so the enhancement does not carry much value IMO.
>
>The filtered FOR !DELETED() is a total miss as it should only be used in optimizing the field where it is indexed on, not the query on the whole. Now its behanving as an INDEX ON DELETED() TAG del, even with worse performance depending on the width of the field on which it is indexed.
>
>It is a total miss to discuss the INDEX ON DELETED() thing in the helpfile at all, since its suggests it is good practise to use them. In fact it is not, it is bad practise unless you've got some very specific circumstance where it is justified.
>
>Rather than doing this they should have included an in depth explanation about good index practises. If they've done that in the past, it would have avoided a lot of grief, misunderstanding and performance problems.
>
>Walter,