Command SET RUSHMORE Foundation

The Rushmore technology is a data access technique that operates on index files instead of database files to optimize data access. Rushmore can be used with Database Engines that handle NTX or CDX indexes. This technique takes advantage of the information stored in an index which allows for a very effient query optimization to identify a subset of records.

Since CDX indexes use a compression technique that shrinks the indexes to one-sixth of their uncompressed size (=NTX format), more information of a CDX index than of a NTX index can be held in memory, which leads to reduced disk access. It is recommended to use CDX indexes to take maximum advantage of the Rushmore optimization.

Simple indexes and simple expressions

The following explanations assume a database having this structure:

A simple index is of the form: INDEX ON <field> TAG <name>. This is the type of index that Rushmore likes best. In order to utilize Rushmore with this type of index, a simple expression including an indexed field is required. If an index exists for the field FIRSTNAME, for example, the following commands are optimized:

SET FILTER TO FIRSTNAME='James' 
COUNT ALL FOR FIRSTNAME='James' 

If an operand on the left or right side of an expression is identical with an existing index expression, the command using the expression can be optimized by Rushmore, regardless of whether or not the index is the controlling index. Rushmore utilizes all indexes available in a workarea.

Operator support by Rushmore

The following table lists the comparison operators that benefit from Rushmore technology and to which extent.

Compound or complex indexes

Rushmore supports compound indexes for the data types Character, Numeric, Logic and Date. A compound Character index is for example:

INDEX ON LASTNAME+FIRSTNAME TAG name 

In order to utilize this kind of index expression with Rushmore, the following filter condition could be set:

SET FILTER TO LASTNAME+FIRSTNAME = PadR("Kirk",30) + "James" 

Note that the left operand in the filter condition must be exactly the expression of the index to take advantage of Rushmore. Also bear in mind that the field LASTNAME is a Character field of length 30. Therefore the value on the right side must be prepared to match with the resulting index value. For this example, the following expressions cannot be optimized with Rushmore:

SET FILTER TO UPPER(LASTNAME + FIRSTNAME) = PadR("KIRK",30) + "JAMES" 

SET FILTER TO LASTNAME+FIRSTNAME  = "Kirk James" 

In the first line, the left operand does not match the index expression, and the right operand does not match an index value in the second line.

Compound Filter/Query Expressions

A compound query expression is best described as a set of simple expressions with boolean operators. For example CREDITS>10 .AND. CREDITS<100 is a compound expression of two simple expressions. In general, Rushmore can fully optimize compound expressions if an INDEX is available for any given FIELD. For example LASTNAME='Kirk' .AND. CREDITS>10 is again fully rushmoreable if the indexes LASTNAME and CREDITS are available.

If, for example, only an index for the field LASTNAME is available, the expression is partially rushmoreable because Rushmore is used to calculate the result-set for LASTNAME and then each result-row is evaluated against the non-rushmore-able part of the expression. This technology is called "linear optimization of partially optimizable expressions".

Hint: When using boolean operators, Rushmore creates a single bitmap for the left and right side of an expression and merges them together. Therefore, Rushmore needs two times more RAM to operate on compound query expressions compared to simple expressions.

The following table shows boolean operators which benefit from Rushmore:

However in case your expression is only compound by ORing and not all sub-expressions can be optimized by Rushmore, e.g. no corresponding index is available for each sub-expression, the entire expression is not optimized by Rushmore.

Full and Partial rushmoreable

Assume the following filter expression:

SET FILTER TO LASTNAME = "Kirk" .AND. FIRSTNAME = "James" 

In order for this SET FILTER statement to be fully optimizable, two indexes are required:

INDEX ON FIRSTNAME TAG FNAME 
INDEX ON LASTNAME  TAG LNAME 

This solution allows Rushmore to find the records in the fastest possible time without retrieving a single record from the hardisk.

If there were only an index on FIRSTNAME, but no index on LASTNAME, the expression would be partially optimizable. Rushmore would identify first all records which match the value for FIRSTNAME, and then treat the rest of the expression as a non-optimizable search, i.e. the rest of the search is performed on the subset of records identified in the optimized part of the search.

The difference between full and partial optimization is that with full support no disk access is required to transfer data stored in records of a database from disk to memory in order to evaluate the expression result. In case of partial optimization, Rushmore can identifiy a starting point for the subset generation but must retrieve an unknown portion of records from disk in order to evaluate the expression result.

Rushmore and SET DELETED

Rushmore can optimize a query for deleted records if an INDEX ON Deleted() exists. That means, if a database contains a large number of deleted records, an application gains performance by creating an index on deleted(), or by using the SET DELETED OFF statement before starting a database query.

SET RUSHMORE and SET SMARTFILTER

By default, SET OPTIMIZE, SET SMARTFILTER and SET RUSHMORE are ON, i.e. the database kernel is allowed to use the different optimization technologies based on its internal logic. For example, if the expression is not fully rushmoreable the database optimizer decides to use Rushmore for one part of the expression and Smart Filtering for the rest of the expression to avoid unneccessary reload of records from disk. When SET SMARTFILTER is set to OFF, the database optimizer is using Rushmore but is not allowed to use Smart Filtering for the non-rushmoreable part of the expression. As a consequence, unneccessary record-loads from disk may occur.

SET RUSHMORE and SET OPTIMIZE

Setting SET OPTIMIZE OFF forces the databae to treat any expression as a code block, or in other words, as some sort of black-box. As a consequence, Rushmore is no more applicable because Rushmore technology needs details about the expression itself. However, Smart Filtering works independent of the SET OPTIMIZE setting.