Hi fabio,

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

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,







>>Hi Fabio,
>>
>>>In VFP9 beta not there is trace of this.
>>
>>There is, search for "FOR DELETED()" and you'll find the topic.
>>
>>

CREATE CURSOR test (test I)
>>APPEND BLANK
>>INDEX ON Test TAG TEST
>>SET DELETED ON
>>
>>SYS(3054,2)
>>
>>SELECT * FROM Test WHERE Test = 1 NOFILTER
>>
>>SELECT Test
>>DELETE TAG ALL
>>
>>INDEX ON Test TAG TEST FOR NOT DELETED()
>>SELECT * FROM Test WHERE Test = 1 NOFILTER

>>
>>
>>Walter,
>
>
>Well, but the job it is not completed,
>and this is't true for low level data operations,
>VFP9 don't optimize the FOR clause !
>
>VFPT it wants to abandon these commands?
>

>SET DELETED ON
>SET TALK OFF
>CLEAR
>CREATE CURSOR TESTDELETED (AA I DEFAULT RAND()*1000)
>FOR k=1 TO 1000000
>	APPEND BLANK
>	IF RAND()<0.5
>		DELETE
>	ENDIF
>NEXT
>? 'no index'
>TRYTEST()
>INDEX ON aa  TAG taa
>? 'full index'
>TRYTEST()
>DELETE TAG ALL
>INDEX ON aa  TAG taa FOR NOT DELETED()
>? 'filtered index'
>TRYTEST()
>
>PROCEDURE TRYTEST
>SET ORDER TO
>
>t1=SECONDS()
>  COUNT FOR aa<1
>? _tally,SECONDS()-t1
>
>t1=SECONDS()
> ? LOOKUP(AA,1,AA), SECONDS()-t1
>
>T1=SECONDS()
>SELECT COUNT(*) FROM TESTDELETED WHERE AA<1 INTO ARRAY DUMMY
>? _tally,SECONDS()-t1
>

>
>You can try this into VFP8,
>a bug of the LOOKUP() function ( VFP don't check a FOR index condition )
>allow FOR NOT DELETED() optimization before VFP9.
>
>VFP it is now a confused programming environment,
>if VFP9 RTM don't fixed this new design bug ( FOR optimization )
>this increase the confusion.
>
>No good.
>
>Fabio