Yup, Craig is right about the Not Deleted() make sure that your index is indexed on Deleted() = .T. however VFP Help states SET DELETED ON. See quote:
Similarly, Rushmore cannot use an index created with a NOT condition. For example, the following expression can be optimized:
INDEX ON DELETED() TAG DEL
But this one cannot:
INDEX ON NOT DELETED() TAG NOTDEL
In the special case that you want to exclude delete records from a query, using an index like the first example above will speed up operations when you've set SET DELETED to ON.
End quote
Thanks Craig for spotting my deliberate mistake :-o
For those of you looking into this issue more closely then baear in mind the other caveat from VFP Help see below
Quote
If you use a scope clause in addition to an optimizable FOR clause expression, the scope must be set to ALL or REST to take advantage of Rushmore. The NEXT or RECORD scope clauses disable Rushmore. Since the default scope is ALL for most commands, Rushmore works when you omit the scope clause.
Rushmore can use any open indexes except for filtered and UNIQUE indexes.
Note For optimal performance, don?t set the order of the table.
Creating index or tags automatically sets the order. If you want to take maximum advantage of Rushmore with a large data set that must be in a specific order, issue SET ORDER TO to turn off index control, then use the SORT command.
END Quote
From this it is a strong possibility that the old way is probably utilising the gains of Rushmore Optimization whereas the "New" way is not or is only partially optimizing. This warrants a closer examination.
<i><font color="#663300">If you index with FOR NOT DELETED(), it won't help. This tag will be ignored by Rushmore. Index on DELETED() and make sure to SET DELETED OFF before running the query.
=================
<i><font color="#663300">Hi Graeme,
I don't have access to such a large table, but my thoughts would be to check if the sql is being optimized have you a index/filter for Not Deleted() = .T. for example. This may explain.
Just a thought!
</font></i>
©2001 Geof whitham |
