>In help it says that empty() is not optimizable and that "#" is.
>
>I originally was thinking this wasn't correct because I had a select-sql clause that was much faster if my expression was "ino_acct = nAcct and !empty(ino_total)" instead of "ino_acct = nAcct and ino_total # 0.00".
>
>My invoice table has around a half million records.
>
>Anyway, after awhile I came to the conclusion, (whether its correct or not I don't know), that the "#" operator is IN FACT optimizable and that is why its slower! Is this possible? When I use the non-optimizable empty() function rushmore only acts upon my first expression. Is this correct?

That is quite correct. VFP doesn't use "cost-based optimization"; the rule is very simple: "If there is an index, use it!" whether it speeds things up or not. The index will slow things down if it returns many records! This is because Visual FoxPro has to get the keys for the index expression, even if it doesn't get the records, for the "optimization". Then, VFP creates a bitmap (i.e., one bit for each record), and next it BIT-ANDs the results together, to see what records it needs to fetch.

How many records do you think are returned for "ino_total # 0.00"? Too many, of course.

If you delete the index, or use a "non-optimizable" expression, Visual FoxPro will "optimize" only the other, "optimizable" part, fetch the corresponding records (few of them, hopefully), and finally apply the remaining, "non-optimizable", expression directly to the records.

>If so, I wonder if there are any golden rules of thumb as to when I should or shouldn't use optimization.

The rule of thumb is to delete any index if it returns too many records for a given key value.

Your case is a little more complicated; I assume you have an index expression on "ino_total". Any numeric value would return only a few records; unfortunately, "#" is also "optimized" by VFP. However, in this case, the workaround is simple: as you pointed out, you can use "empty()". This should work, unless you have an index on "empty(ino_total)". As you have probably guessed already, it is better not to have this particular index.
>
>I hope this makes sense,

To me, it makes a lot of sense.

Remember, too, *not* to include any index on the expression deleted(); this will also return too many records.

Hilmar.