Using Rushmore Query Optimization to Speed Data Access

Article
07/09/2007

To help you optimize the performance of your applications, Visual FoxPro includes Rushmore Query Optimization data access technology. Using Rushmore technology, you can run certain complex table operations hundreds or even thousands of times faster than without it.

Understanding Rushmore Query Optimization Technology

Rushmore Query Optimization technology is a data access technique that uses standard Visual FoxPro indexes to optimize access to data. You can use Rushmore with any Visual FoxPro index, including FoxPro 1.x (.idx) indexes, compact (.idx) indexes, and compound (.cdx) indexes.

Both .cdx and compact .idx indexes use a compression technique that produces indexes as small as one-sixth the size of uncompressed old-format indexes. Visual FoxPro can process a compressed index faster because it requires less disk access, and because more of the index can be buffered in memory. Although Rushmore Query Optimization, like other file access techniques, benefits from the smaller size of compact indexes, it also functions very well with indexes in older formats.

When Visual FoxPro processes very large tables on computers with only the minimum amount of RAM, Rushmore might not find sufficient memory to operate. In that case, Visual FoxPro might display a warning message ("Not enough memory for optimization"). Although your program will function correctly and not lose any data, the query will not benefit from Rushmore optimization.

In its simplest form, Rushmore speeds the performance of single-table commands using FOR clauses that specify sets of records in terms of existing indexes. Also, Rushmore can speed the operation of certain commands such as LOCATE and INDEX. For a complete list of optimizable commands, see the next section, "Using Rushmore Query Optimization with Tables."

Visual FoxPro SQL commands use Rushmore as a basic tool in multi-table query optimization, using existing indexes and even creating new ad-hoc indexes to speed queries.

Using Rushmore Query Optimization with Tables

Use Rushmore to optimize data access according to the number of tables involved. When you access single tables, you can take advantage of Rushmore anywhere that a FOR clause appears. When you access multiple tables, SELECT - SQL queries supersede all Rushmore optimizations. In an SQL command, Visual FoxPro decides what is needed to optimize a query and does the work for you. You don't need to open tables or indexes. If SQL decides it needs indexes, it creates temporary indexes for its own use.

To use Rushmore Query Optimization

To access data from a single table, use a FOR clause in a command such as AVERAGE, BROWSE, or LOCATE, or use SQL commands to update tables. For a complete list of commands that use the FOR clause, refer to the table below.

-or-
To access data from more than one table, use the SELECT - SQL, DELETE - SQL, and UPDATE - SQL commands.

The following table lists commands that use FOR clauses. Rushmore is designed so that its speed is proportional to the number of records retrieved.

Potentially Optimizable Commands with FOR Clauses

AVERAGE	BLANK
BROWSE	CALCULATE
CHANGE	COPY TO
COPY TO ARRAY	COUNT
DELETE	DISPLAY
EDIT	EXPORT TO
INDEX	JOIN WITH
LABEL	LIST
LOCATE	RECALL
REPLACE	REPLACE FROM ARRAY
REPORT	SCAN
SET DELETED	SET FILTER
SORT TO	SUM
TOTAL TO

If you use a scope clause in addition to an optimizable FOR clause expression, the scope must be set to ALL or REST to take advantage of Rushmore. The NEXT or RECORD scope clauses disable Rushmore. Because the default scope is ALL for most commands, Rushmore works when you omit the scope clause.

Rushmore can use any open indexes except for filtered and UNIQUE indexes.

Note

For optimal performance, don't set the order of the table.

Creating index or tags automatically sets the order. If you want to take maximum advantage of Rushmore with a large data set that must be in a specific order, issue SET ORDER TO to turn off index control, then use the SORT command.

Indexing Effectively for Rushmore Query Optimization

Rushmore cannot take advantage of all indexes. If you use a FOR clause in the INDEX command, Rushmore cannot use the index for optimization. For example, because it contains a FOR clause, this statement cannot be optimized, INDEX ON ORDNUM FOR DISCOUNT > 10 TAG ORDDISC.

Similarly, Rushmore cannot use an index created with a NOT condition. For example, the this expression can be optimized, INDEX ON DELETED() TAG DEL, But this one cannot, INDEX ON NOT DELETED() TAG NOTDEL.

In the special case that you want to exclude delete records from a query, using an index, as in the first example earlier, will speed up operations when you've set SET DELETED to ON.

Rushmore Optimization and Code Pages

Visual FoxPro does not use existing character indexes for tables created with a non-current code page. Instead, Visual FoxPro builds temporary indexes to ensure correct results. This can result in non-optimization of SQL or other commands. To prevent this, ensure that the current Visual FoxPro code page returned by CPCURRENT( ) Function matches the table's code page returned by CPDBF( ) Function. This requires either changing the current Visual FoxPro code page, or changing the table's code page. For information about specifying the current Visual FoxPro code page, see Understanding Code Pages in Visual FoxPro. For information about specifying the code page for a table, see How to: Specify the Code Page of a .dbf File. If you cannot change either the Visual FoxPro codepage or the table codepage to match, you can force optimization to work as it did in Visual FoxPro 8 and prior versions using the SET ENGINEBEHAVIOR Command with either 80 or 70 as a parameter.

Operating Without Rushmore Query Optimization

Data retrieval operations proceed without Rushmore optimization in the following situations:

When Rushmore cannot optimize the FOR clause expressions in a potentially optimizable command.
When a command that might benefit from Rushmore contains a WHILE clause.
When memory is low. Data retrieval continues, but is not optimized.

Disabling Rushmore Query Optimization

Though you rarely want to, you can disable Rushmore. When you issue a command that uses Rushmore, Visual FoxPro immediately determines which records match the FOR clause expression. These records are then manipulated by the command.

If a potentially optimizable command modifies the index key in the FOR clause, the recordset on which Rushmore is operating can become outdated. In this case, you can disable Rushmore to ensure that you have the most current information from the table.

To disable Rushmore for an individual command

Use the NOOPTIMIZE clause.

For example, this LOCATE command is not optimized:
```
LOCATE FOR DueDate < {^1998-01-01} NOOPTIMIZE
```

You can globally disable or enable Rushmore for all commands that benefit from Rushmore, with the SET OPTIMIZE command.

To disable Rushmore globally

Use the following code:
```
SET OPTIMIZE OFF
```

To enable Rushmore globally

Use the following code:
```
SET OPTIMIZE ON
```

The default setting of Rushmore optimization is ON.

Optimizing Rushmore Expressions

Rushmore technology depends on the presence of a basic optimizable expression in a FOR clause or in an SQL WHERE clause. A basic optimizable expression can form an entire expression or can appear as part of an expression. You can also combine basic expressions to form a complex optimizable expression.

Creating Basic Optimizable Expressions

A basic optimizable expression takes one of the two following forms:

            eIndex relOp eExp

-or-

            eExpr relOp eIndex

A basic optimizable expression has the following characteristics:

eIndex exactly matches the expression on which an index is constructed.
eExpr is any expression and can include variables and fields from other unrelated tables.
relOp is one of the following relational operators: <, >, =, <=, >=, <>, #, ==, or !=. You can also use the ISNULL( ), BETWEEN( ), or INLIST( ) functions (or their SQL equivalents such as IS NULL, and so on).

You can use BETWEEN( ) or INLIST( ) in the following two forms:

BETWEEN(eIndex, eExpr, eExpr)

-or-

INLIST(eIndex, eExpr [, eExpr, eExpr, ...])

Note

ISBLANK() and EMPTY() are not optimizable by Rushmore.

If you create the indexes firstname, custno, UPPER(lastname), and hiredate, each of the following expressions is optimizable:

firstname = "Fred"
custno >= 1000
UPPER(lastname) = "SMITH"
hiredate < {^1997-12-30}

An optimizable expression can contain variables and functions that evaluate to a specific value. For example, using the index addr, if you issue the command STORE "WASHINGTON AVENUE" TO cVar, then the following statements are also basic optimizable expressions:

ADDR = cVar
ADDR = SUBSTR(cVar,8,3)

Understanding When Queries Are Optimized

It is important to understand when queries will be optimized and when they will not. Visual FoxPro optimizes search conditions by looking for an exact match between the left side of a filter expression and an index key expression. Therefore, Rushmore can optimize an expression only if you search against the exact expression used in an index.

For example, imagine that you've just created a table and are adding the first index using a command such as the following:

USE CUSTOMERS
INDEX ON UPPER(cu_name) TAG name

The following command is not optimizable, because the search condition is based on the field cu_name only, not on an expression that is indexed:

SELECT * FROM customers WHERE cu_name ="ACME"

Instead, you should create an optimizable expression using a command such as the following, in which the expression for which you are searching exactly matches an indexed expression:

SELECT * FROM customers WHERE UPPER(cu_name) = "ACME"

Tip

To determine the level of Rushmore optimization being used, call SYS(3054).

Combining Basic Optimizable Expressions

You can combine simple or complex expressions based on the FOR clause or WHERE clause to increase data retrieval speed, if the FOR expressions have the characteristics of basic optimizable expressions.

Basic expressions might be optimizable. You can combine basic expressions using the AND, OR, and NOT logical operators to form a complex FOR clause expression that might also be optimizable. An expression created with a combination of optimizable basic expressions is fully optimizable. If one or more of the basic expressions are not optimizable, the complex expression might be partially optimizable or not optimizable at all.

A set of rules determines if an expression composed of basic optimizable or non-optimizable expressions is fully optimizable, partially optimizable, or not optimizable. The following table summarizes Rushmore query optimization rules.

Combining Basic Expressions

Basic Expression	Operator	Basic Expression	Query Result
Optimizable	AND	Optimizable	Fully Optimizable
Optimizable	OR	Optimizable	Fully Optimizable
Optimizable	AND	Not Optimizable	Partially Optimizable
Optimizable	OR	Not Optimizable	Not Optimizable
Not Optimizable	AND	Not Optimizable	Not Optimizable
Not Optimizable	OR	Not Optimizable	Not Optimizable
—	NOT	Optimizable	Fully Optimizable
—	NOT	Not Optimizable	Not Optimizable

You can use the AND operator to combine two optimizable expressions into one fully optimizable expression:

FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}      && Optimizable

In this example, the OR operator combines a basic optimizable expression with an expression that is not optimizable to create an expression that is not optimizable:

FIRSTNAME = "FRED" OR "S" $ LASTNAME      && Not optimizable

Using the NOT operator on an optimizable expression creates a fully optimizable expression:

NOT FIRSTNAME = "FRED"      && Fully optimizable

You can also use parentheses to group combinations of basic expressions.

Combining Complex Expressions

Just as you can combine basic expressions, you can combine complex expressions to create a more complex expression that is fully optimizable, partially optimizable, or not optimizable. You can then combine these more complex expressions to create expressions that again might be fully or partially optimizable, or not optimizable at all. The following table describes the results of combining these complex expressions. These rules also apply to expressions grouped with parentheses.

Combining Complex Expressions

Expression	Operator	Expression	Result
Fully Optimizable	AND	Fully Optimizable	Fully Optimizable
Fully Optimizable	OR	Fully Optimizable	Fully Optimizable
Fully Optimizable	AND	Partially Optimizable	Partially Optimizable
Fully Optimizable	OR	Partially Optimizable	Partially Optimizable
Fully Optimizable	AND	Not Optimizable	Partially Optimizable
Fully Optimizable	OR	Not Optimizable	Not Optimizable
—	NOT	Fully Optimizable	Fully Optimizable
Partially Optimizable	AND	Partially Optimizable	Partially Optimizable
Partially Optimizable	OR	Partially Optimizable	Partially Optimizable
Partially Optimizable	AND	Not Optimizable	Partially Optimizable
Partially Optimizable	OR	Not Optimizable	Not Optimizable
—	NOT	Partially Optimizable	Not Optimizable
Not Optimizable	AND	Not Optimizable	Not Optimizable
Not Optimizable	OR	Not Optimizable	Not Optimizable
—	NOT	Not Optimizable	Not Optimizable

You can combine fully optimizable expressions with the OR operator to create one expression that is also fully optimizable:

* Fully-optimizable expression
(FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ;
   OR (LASTNAME = "" AND HIREDATE > {^1996-12-30})

To create partially optimizable expressions, combine a fully optimizable expression with an expression that is not optimizable. In the following example, the AND operator is used to combine the expressions:

* Partially-optimizable expression
(FIRSTNAME = "FRED" AND HIREDATE < {^1997-12-30}) ;
   AND "S" $ LASTNAME

Partially optimizable expressions can be combined to create one expression that is also partially optimizable:

* Partially-optimizable expression
(FIRSTNAME = "FRED" AND "S" $ LASTNAME) ;
   OR (FIRSTNAME = "DAVE" AND "T" $ LASTNAME)

Combining expressions that are not optimizable creates an expression that is also not optimizable:

* Expression that is not optimizable
("FRED" $ FIRSTNAME OR "S" $ LASTNAME) ;   
   OR ("MAIN" $ STREET OR "AVE" $ STREET)