>>>>Hi all,
>>>>
>>>>I just upgraded an application from VFP 7 to VFP 9 in a customer.
>>>>So, I created a new index for each table:
>>>>
>>>>Index on Deleted() Tag DeletedTag Binary
>>>>
>>>>This index has collate as MACHINE. My others indexes (like name, for example) have collate as GENERAL.
>>>>
>>>>So I have a problem:
>>>>
>>>>If I have two collates in my indexes, how can I set the right collate to use rushmore optimization?
>>>>
>>>>Any idea?
>>>>
>>>>By now, I just deleted this index and it works great - but I want moreeeeeeee! ;-)
>>>>
>>>>TIA
>>>
>>>An not character index, it doesn't have collate.
>>>However this index is useless,
>>>you add
>>>
>>>FOR NOT DELETED()
>>>
<
>>> to the primary index.
>>
>>Fabio,
>>
>>From VFP 9 Help, "If you use a FOR clause in the INDEX command, Rushmore cannot use the index for optimization." So, wouldn't that index be even more useless?
>>
>>Regards,
>>Jim
>
>
>Jim,
>I don't believe in the documentation.
>
>
>CLEAR
>SET ESCAPE ON
>SET TALK OFF
>
>CREATE CURSOR testdeleted (id i autoincr )
>
>DIMENSION aFor[1000000,1]
>APPEND FROM array aFor FIELDS EXCEPT id
>RELEASE aFor
>
>* delete 50%
>DELETE ALL FOR RAND()<0.5
>
>SET DELETED ON
>LOCATE
>SKIP RECCOUNT()/2/2
>idNotDeleted=id
>? "this is a not deleted id",id at 30
>
>SYS(3054,11)
>
>? "***** ONE INDEX ******"
>DELETE TAG ALL
>INDEX ON id TAG pk
>* Partial
>Test()
>
>
>? "***** TWO INDEXES ******"
>DELETE TAG ALL
>INDEX ON id TAG pk
>INDEX ON DELETED() TAG tdel BINARY
>* Full
>Test()
>
>? "**** ONE INDEX ONLY ******"
>DELETE TAG ALL
>INDEX ON id FOR NOT DELETED() TAG pk
>* Full
>Test()
>
>
>
>PROCEDURE test
>SYS(3054,11)
>SELECT * FROM testdeleted X WHERE id=m.idNotDeleted INTO ARRAY rr
>
>SYS(3054,0)
>
>t1=SECONDS()
>FOR k=1 TO 10
> SELECT COUNT(*) FROM testdeleted X WHERE id>m.idNotDeleted INTO ARRAY rr
>NEXT
>? "COUNT A RANGE OF pk ",SECONDS()-t1
>
>
>t1=SECONDS()
>REINDEX
>? "REINDEX TIME ",SECONDS()-t1
>?
>
>
>Now, for DELETED() condition it has sense create a binary index when
>its natural place is in the filter of the primary index.
>
>Of course the Team has not finished the job,
>and a lot of things have remained to do, but this is another discourse.
Fabio,
That's very interesting. I thought that maybe the order the tests were run in might have affected the results so I reversed the order of the tests. The results were almost identical to the first set.
But, of course, there is still the problem Calvin Hsia pointed out in his Blog regarding an unbalanced index. In your example, there were as many deleted as non deleted records. Running the code with no deleted records, your third test took twice as long as the second.
Regards,
Jim
