SET OPTIMIZE, Set(“Optimize”)

Here’s another command to add to your “Don’t ever use this” list. Even on those rare occasions when you might need the effects of SET OPTIMIZE OFF, it’s better to localize it by adding NOOPTIMIZE to the relevant commands than to make a global change.

SET OPTIMIZE determines whether FoxPro attempts to use Rushmore to optimize commands. The default is ON, as it should be. SET OPTIMIZE affects all commands with FOR clauses.

Usage

SET OPTIMIZE ON | OFF
cOptimizeSetting = SET( "OPTIMIZE" )

We had a hard time imagining any case where you’d want to SET OPTIMIZE OFF, but we asked around and heard a few stories. Basically, very, very rarely, you may have a large enough data set and a complicated enough condition that Rushmore can’t figure out what to do with it and gets bogged down. In such cases, turning optimization off results in better performance because FoxPro doesn’t bother to try to figure out how to optimize. (Two of our reporters indicated they had problems with complex combinations of SCAN FOR and SET FILTER, which actually gave bad results because of Rushmore failing.) Similarly, when dealing with a view that has no indexes, turning off Rushmore may speed things up because FoxPro doesn’t even bother to see whether it can optimize.

Another time to consider turning off optimization is when memory and disk space are insufficient for Rushmore to do its thing. Again, setting OPTIMIZE OFF in that case prevents Rushmore from giving it the old college try before doing it the slow way.

The bottom line here is that SET OPTIMIZE OFF or NOOPTIMIZE is something to try when all else has failed. Before you do so, try rebuilding your indexes; the problem may be corrupted indexes.

Finally, the documentation talks about possibly needing to turn off optimization in cases where you change the value of an index tag used for optimization. We tried to get bad results in such a case and found that FoxPro seemed to be smart enough to rebuild its bitmap along the way.

As for SET(“OPTIMIZE”), it returns the current setting, so you can save it and reset it later.

See Also

Set, Set()