From Hack Fox: The Rushmore optimization technology introduced in FoxPro 2.0 is based on indexes. Rushmore examines index files to determine which records meet the conditions for a particular command. So, in order to make things fast, it's important to create the right indexes and to write code that takes advantage of those indexes.
Rushmore does NOT use filtered indexes. Rushmore also does not use indexes with NOT in the key expression.
Rushmore can optimize the SET FILTER command, and any command involving a FOR clause, as well as SELECT-SQL. The secret is to make the left-hand side of each expression in the filter, FOR or WHERE clause exactly match an existing index tag.
Conventional wisdom says that you should also add an index tag on DELETED() so if you have SET DELETED ON, Rushmore will be able to use this tag to speed up finding these records to ignore or bypass.
Chris Probst, in his article "Rushmore: Less is More" (Foxpro Advisor - May 99 issue), contends that in some cases this may be counterproductive, i.e. for queries where there are few deleted records in a big table. What happens is that if you use a tag on DELETED(), and with SET DELETED ON, the condition "AND DELETED() = .F." gets appended to every query bringing down a lot more records for the query optimizer to work with, therefore slowing down the process. See Chris Probst 's article for a lot more details on this.
Actually, the problems arise when running VFP SQL statements. VFP sucks down the portion of the index tag that matches what the query is asking for. For logicals, as in DELETED(), the entire tag comes to the workstation. That could be a pretty substantial amount of data for large tables. This is also applicable when doing TABLEUPDATE() for local views. Make sure the SQL WHERE clause update includes 'Key fields only' otherwise VFP will build this massive SQL command and include all sorts of optimized expressions that will kill your performance (and your network!). -- Chris Probst
See also Understanding Rushmore
Contributors: Alex Feldstein Chris Probst
Category VFP Commands
( Topic last updated: 1999.08.23 09:40:00 AM )