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 http://blogs.msdn.com/calvin_hsia/archive/2005/01/05/347388.aspx

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:

   SET COLLATE TO "MACHINE"
   CREATE TABLE Junk ( cField C(5) )
   INDEX ON cField TAG cField
   SET COLLATE TO "GENERAL"
   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:

   INDEX ON Field TAG Field FOR NOT DELETED()

For VFP9 this limitation is now lifted according to http://www.code-magazine.com/article.aspx?quickid=0404022&page=3

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
   SELECT * FROM Junk WHERE NOT lField

Also, the expression of the left side on the search expression must match exactly the index expression. 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)

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
   SELECT * FROM Table WHERE NOT EMPTY(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 ...

   SELECT * FROM TABLE WHERE UPPER(Field) = SPACE(nFieldLen)

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 everytime it moves the record pointer.

One final note, grids 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 is 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