Wiki Home

Understanding Rushmore

Namespace: WIN_COM_API
Sometimes Rushmore is difficult to understand if you don't know the rules it follows and if you are not aware of the differences between xBase-Rushmore and SQL-Rushmore.

Interesting article about indexes may be found in Calvin Hsia blog

Rushmore is based on indexes, therefore the most important part to understand is which indexes Rushmore uses and which it doesn't use. An index is only used, if

a) it has the same collate sequence as the current collate sequence. The following query is therefore not optimized:
   CREATE TABLE Junk ( cField C(5) )
   INDEX ON cField TAG cField
   SELECT * FROM Junk WHERE cField = "Value"

b) the index does not have any filter attached to it. That means an index like this is not used for optimization:

For VFP9 this limitation is now lifted according to

c) the index expression does not contain the NOT keyword. In the following sample the index is not used for Rushmore optimization, even though the expression matches the WHERE expression:
   CREATE TABLE Junk ( lField L )
   INDEX ON NOT lField TAG lField

Also, the expression of the left side on the search expression must match exactly the index expression.
At least as far back as VFP 7, the optimizable expression can be on either side of the comparison. VFP 9 help backs up this assertion. In the topic "Using Rushmore Query Optimization to Speed Data Access," the section "Creating Basic Optimizable Expressions" shows expressions with the expresion matching a tag on either side of the relational operator. I'm not sure when this was changed--TamarGranor
The following filter is not optimizable:
   INDEX ON UPPER(Field) TAG Field
   SET FILTER TO Field = UPPER(m.lcValue)

but this one is...
   INDEX ON UPPER(Field) TAG Field
   SET FILTER TO UPPER(Field) = UPPER(m.lcValue)

In this example, the left side of the operator matches the index expression, but the right side is an expression. That means that the expression will be evaluated per row. It is better to make the right side a constant.
  INDEX ON UPPER(Field) TAG ixField
  lcValue = upper(m.lcValue)
  SET FILTER TO UPPER(Field) = m.lcValue
-- Mike Yearwood

There was a time where Fox 2.x did not care about the left side of the operator. -- Mike Yearwood

For xBase this means, too, that the filter expression must not contain the alias, otherwise VFP does not always use the index for optimization. In a SQL command you can include the alias without affecting Rushmore.

In xBase, an optimizable expression can only refer to one table. FoxPro is not able to optimize filters that span several tables. In other words, you can't set an optimizable filter on the parent table of a relation that filters records in the child table. With SQL FoxPro can use Rushmore for several tables.

Rushmore can only optimize three functions natively: INLIST(), BETWEEN() and ISNULL(), because those are internally converted into AND and OR statements, or in case of ISNULL() into a direct comparison. Please note especially, that EMPTY() is not optimizable, as this function is often used in SELECT statements and filter expressions. If you want to optimize a filter expression that uses a function, you must create an index on this function. For example, the following query is optimizable, because there's an index on EMPTY(Field):
   INDEX ON EMPTY(Field) TAG Field

without this index, the query would not be optimizable. Usually you can avoid using empty by using the appropriate empty value, eg. .F. for logical values, {} for date values, 0 for numeric values and SPACE(nFieldLen) for character fields.

As there has been much discussion of the practicality of indexes that consist of logical values, such as Chris Probst 's Foxpro Advisor Article and Charlie Schreiner's benchmarks on deleted tags, the previous example would be a poor choice for an index. The use of ...

would be optimized and the same index could be used for many other purposes. -- Mike Yearwood

Also, there are more subtle differences between xBase and SQL Rushmore. When you create an index on a UDF (user defined function), SQL can use this index, but won't call your UDF, xBase, on the other hand, doesn't use the index and calls your function for every record. And, NOT == is optimizable in xBase, but not in SQL:
   INDEX ON cField TAG cField
   * optimizable
   LOCATE FOR NOT cField == "Value"
   * not optimized
   SELECT * FROM Table WHERE NOT cField == "Value"

How does the above reconcile with this extract from the VFP Help?
Using the NOT operator on an optimizable expression creates a fully optimizable expression:

NOT FIRSTNAME = "FRED"      && Fully optimizable
-- Jim Nelson

Related to this, if any filter expression in a SQL command uses ==, the result set is not filtered, FoxPro always creates a physical cursor:
   SELECT * FROM Table WHERE cField = "Value" INTO CURSOR Junk
   ? DBF("Junk")    && prints "Table.Dbf"
   SELECT * FROM Table WHERE cField == "Value" INTO CURSOR Junk
   ? DBF("Junk")    && prints "XYZ1230K.TMP"

Even though some articles imply this, FoxPro doesn't only use the index to find out which records match a criteria, even when the criteria is fully optimized. Instead it first creates a set of records that match the criteria using Rushmore and then it reads every matching record and re-evaluates the search criteria again to find out whether the record still matches it. This prevents getting wrong results when another user changed the record in the meantime. The only exception are count operations like COUNT TO or SELECT CNT(*). Here VFP uses only the index to determine the number of records. If a command doesn't return a result set immediately, like SCAN FOR or SQL-SELECT with a filtered result set, Visual FoxPro performs this check every time it moves the record pointer. -- Christof Wollenhaupt

I merely wish to present some evidence and my conclusion. I have a local table called speed.dbf with 25,877,618 records. I setup processmonitor to examine access to the .dbf and the .cdx.
The table is indexed on an integer field with this index command INDEX ON SPD_ID TAG SPD_ID.
I created a filter SET FILTER TO INLIST(spd_id,1,6469404,12938809,19408213,25877618). Upon hitting enter, procmon showed no events - that is as expected. It's what Christof means above by a command that doesn't return a result set immediately. I then issued the LOCATE command (instead of GO TOP). Procmon listed many events. 1 and only 1 read of the .DBF. Then 20 reads of the .CDX. It seems for each of the 5 values, it did a seek operation requiring a few steps each. It seems very clear that Fox is only using the index to initially "create a set of records that match the criteria using Rushmore" and it does re-evaluates the search criteria again to find out whether the record still matches it" when the record pointer moves. -- Mike Yearwood

One final note, grids (prior to VFP 9) are not Rushmore aware! Even if you have a fully optimizable filter expression, Visual FoxPro reads the table/cursor sequentially until it finds as many records as it needs to display in the grid.

In VFP9 grids are now Rushmore aware.

The danger was that, in a large table, if there are 3 records in the filter, but the grid is supposed to display 10 rows, the remaining 7 empty rows cause massive churning. -- Mike Yearwood
Contributors: Christof Wollenhaupt, Mike Yearwood
Category VFP Commands Category 3 Star Topics
( Topic last updated: 2015.03.27 02:24:47 PM )