FoxFAQ Pt 3 Rushmore

FoxFAQ Pt 3 Rushmore

Post by an.. » Mon, 31 Jul 1995 04:00:00

June 30th, 1995
Comment and suggestions are invited.


0.0 What is Rushmore?
0.1 What isn't Rushmore?
0.2 Why is it called Rushmore?

1.0 How do I turn on Rushmore?
1.1 Where can I utilize Rushmore?
1.2 Where can't I utilize Rushmore?
1.3 How do I turn off Rushmore?

2.0 Simple indexes and Rushmore.
2.1 Compound indexes and Rushmore.
2.2 Complex indexes and Rushmore.

3.0 Using functions in indexes.
3.1 Using user-defined functions in indexes.
3.2 Using functions from .plb files in indexes.

4.0 What is an "optimizable expression"?
4.1 Fully optimizable expressions.
4.2 Partially optimizable expressions.
4.3 Non-optimizable expressions.

5.0 Rushmore and the SEEK statement.
5.1 Rushmore and the LOCATE statement.
5.2 Rushmore and the SET ORDER TO statement.
5.3 Rushmore and the SET RELATION TO statement.
5.4 Rushmore and the SET DELETED statement.

[Note each of these sections is seperate with "******"]

0.0 What is Rushmore?
    The "simple" answer:  Rushmore is this little bit of magic that
    you main-frame database access speeds on a lowly PC.

    The "more complex" answer:  Rushmore is a data-access method that
    binary selection critera and better index-manipulation methods to
    faster resolution of searches.

    [If you want to know what the "more complex" answer is really saying,
    take a graduate course in database programming.]

    The "correct" answer:  Rushmore is a mountain in South Dakota which
    been carved into the faces of several presidents. :]

0.1 What isn't Rushmore?
    Rushmore has nothing to do with satanism.
    Rushmore has nothing to do with the power tools.
    Rushmore has nothing to do with your report layouts.
    Rushmore has nothing to do with your mouse or keyboard.

0.2 Why is it called Rushmore?
    Oddly enough, the design team came up with the "code-name" Rushmore
    after spending a night watching the movie "North By Northwest."

1.0 How do I turn on Rushmore?
    You don't.  Any time you can include a FOR clause in a statement,
    Rushmore will try to optimize the expression.

    Some exceptions apply.  For example, Rushmore will not activate if
    you have a statement with a WHILE clause in it.

    Rushmore works best with .CDX indexes, but it will utilize any open
    .IDX or compact .IDX indexes that it can get its greedy little hands

1.1 Where can I utilize Rushmore?
    You can utilize Rushmore in any FoxPro statement that allows a FOR
    clause, or in an SQL statement.  If you are dealing with databases
    of any size, you should utilize Rushmore as often as possible.

    Note:  The only way to utilize Rushmore when doing multi-database
    access is through the SQL SELECT statement.

1.2 Where can't I utilize Rushmore?
    Basically, if you can't include a FOR clause, you can't use Rushmore.

    Rushmore also can't be used if you have a WHILE clause in your
        or if you are doing a SELECT DISTINCT ... statement,
    or if you are doing a LOCATE on the child table in a multi-table

    Note that SEEK and GOTO do not utilize Rushmore.

1.3 How do I turn off Rushmore?
    In some rare instances, you may want to turn off Rushmore so that it
    doesn't try to optimize your expression.

    In order to do so there are two methods:

    SET OPTIMIZE ON|OFF - This turns Rushmore on/off until the next SET
    OPTIMIZE statement is executed.  It is probably not a good idea to
    use this statement.

    There is another way to disable Rushmore.  Any statement that can
    utilize Rushmore has a NOOPTIMIZE clause.  This clause will disable
    Rushmore for that statement.  Note that if you have a multi-line
    statement, such as:

    SELECT db1
       SELECT db2
       LOCATE FOR date1 = m.mydate
       WHILE FOUND()
       SELECT db1

Rushmore will be disabled for the db1 LOCATE, but will be active for the

NB:  Be very wary of using GOTO, LOCATE, SEEK, SKIP or any other command
that may move the record pointer (like APPEND, or SQL-SELECT) in any
databases that are linked using SET RELATION TO.  
You may not like the results.

For the following discussions, we have the following database structure:
   fname C(15)
   minit C(1)
   lname C(18)
   dob D
   ssn N(9,0)

2.0 Simple indexes and Rushmore.
    A simple index is of the form:

    INDEX ON ssn TAG ssn

    This is the type of index that Rushmore likes using the "best".  In
    order to utilize Rushmore with this type of tag, all you have to do

    LOCATE FOR ssn = 987654321

    Simple indexes are generally only built on numeric, date, or
    formatted character fields.  For more information on why this is
    so, see sections 2.2, 3.0, and 4.0.

    NOTE: Rushmore works best where indexes that match the selection

2.1 Compound indexes and Rushmore.
    A coumpound index is of the format:

    INDEX ON lname+fname TAG name

    In order to utilize this type of tag with Rushmore, you have to:

    LOCATE FOR lname+fname = "Cochran Keith"

    What's that?  You want to know why you wouldn't use:

    LOCATE FOR name = "Cochran Keith"

    Well, there's a reason for that, and it's even a good one.  The TAG
    parameter is for your usage. Rushmore understands expressions, not

    In order to get Rushmore to work, the expression on the left side of
    the expression must match in essence the expression of the INDEX ON
    statement.  So, the following are things that Rushmore can't utilize
    with our current INDEX statement:

    LOCATE FOR lname+" "+fname = "Cochran Keith"

    For a discussion of how to correctly utilize Rushmore with functions,
    see sections 2.2 and 3.0.

    NB: Special care must be taken when creating compound or complex
    with numeric and date fields.

2.2 Complex indexes and Rushmore.
    A complex index is any index that uses math, string, or other
    as part of the index.  Some examples:

    INDEX ON DTOS(date) TAG cdate
    INDEX ON lname+", "+fname TAG fullname
    INDEX ON STR(ssn) TAG cssn
    INDEX ON ssn+DAY(date) TAG mytag

    In order to get Rushmore to work with these indexes, the index
    must match the expression you are locating on.

3.0 Using functions in indexes.
    As you've seen from the preceeding section, you can utilize any
    function in your index.  The question as far as Rushmore is concerned
    is - should you?

    In most cases, the answer is yes.  The caveat here is that when
    designing your database, you need to be aware of the trade-off
    getting better speed through Rushmore, and taking longer to update
    indexes when you change or add records.

    The other major pitfall with using functions in your indexes is how
    solve for the solutions later.  Most people, if they have an index on
    UPPER(lname+fname), and two input fields, m.last and m.first, will
    attempt to:

    LOCATE FOR UPPER(lname+fname) = UPPER(m.last+m.first)

    This will work, and Rushmore will optimize it, but you won't see any
    real speed increase.  The reason for this is because the
    UPPER(m.last+m.first) is evaluated for EVERY RECORD in the database.

    In order to properly utilize this index, you should: = UPPER(m.last+m.first)
    LOCATE FOR UPPER(lname+fname) =

3.1 Using user-defined functions in indexes.
    Rushmore is able to utilize FoxPro functions in index expressions,
    and it is also able to utilize user-defined functions (udf's) in
    its optimization process.  There are several things you have to be
    aware of, though:

    1) The speed that Rushmore can process is limited by how fast your
       function executes.

    2) Be extremly careful about what your function does.  Your function
       should make no guesses about the current operating environment,
       value of SET EXACT, SET ORDER, SET RELATION, etc.

    3) Your function ***MUST NOT*** modify any databases.  Doing so can
       cause everything from "something strange is happening" to "FoxPro
       must be buggy - it gets stuck in an endless loop."

    4) Be extremly careful about any variables you function may modify.
       It's extremly easy to get the "wrong" result when your function
       modifies one of your search variables.

    5) Your function ***MUST NOT*** move the record pointer in any
       hooked into the database you're using with SET RELATION.  It
       NOT*** move the record pointer in the current database.

3.2 Using functions from .plb files in indexes.
    As long as the function in the .plb is "well behaved", there should
    no problems using it.  Remember the points in section 3.1, though.

4.0 What is an "optimizable expression"?
    An optimizable expression is any expression that Rushmore can attempt
    to use.  How well Rushmore can work with the expression determines if
    the expression is fully, partially, or non-optimizable.

    All of the examples used up to now were either fully optimizable, or
    non optimizable.  Expressions that may be partially optimizable    
generally take the format of:

    LOCATE FOR lname = "Cochran" AND ssn = 987654321

4.1 Fully optimizable expressions.
    In order for our last LOCATE statement to be fully optimizable, we
    would have to:

    INDEX ON lname TAG lname
    INDEX ON ssn TAG ssn

    This is the "best" solution, and will allow Rushmore to find the
    the fastest.

4.2 Partially optimizable expressions.
    If we had the index on ssn, but no index on lname, then our
    would partially optimizable.  Rushmore would activate to locate the
    records which matched our ssn, and then treat the rest of the
    as a non-optimizable search.  Note that Rushmore is smart enough that
    it will solve for the optimizable portions first, and then perform
    the rest of the search on the subset of records it already has.

4.3 Non-optimizable expressions.
    A non-optimizable expression is one that Rushmore can't help.  You
    to avoid these expressions in all but the most extreme cases.  The
    reason is that FoxPro will search every record in the database,
    starting at RECNO() = 1.

5.0 Rushmore and the SEEK statement.
    Basically, the SEEK statement, and the SEEK() function don't interact
    with Rushmore at all.  This can be both a blessing and a curse.  For
    simple indexes, you can get faster results out of SEEK than you
    can out of Rushmore.  But SEEK will not allow you to search on
    multiple fields...

5.1 Rushmore and the LOCATE and SCAN statements.
    Rushmore will almost always speed up your queries when using LOCATE
    or SCAN FOR statements.  To get the best use of Rushmore in these
    statements, you should build simple indexes on the fields you will
    most often search through.

5.2 Rushmore and the SET ORDER TO statement.

    [Many thanks to Evan Simpson ( for helping
    up a misconception about the SET ORDER TO statement for me.]

    (1)The speed of the search will be slower.  This is true whether you
       are using Rushmore or not.
    (2)The first record Rushmore locates (i.e. LOCATE FOR foo = "bar")
       will be the lowest numbered record in the database [RECNO()] that
       matches the search criteria, regardless of the SET ORDER TO

       For example, let's assume we have:
       RECNO()   FOO BAR
             1   AAA BBB
             2   AAA CCC
             3   AAA DDD
             4   AAA CCC

       If you:
       LOCATE FOR bar = "CCC"

       Rushmore will locate RECNO() 2, and then RECNO() 4.

       If you:
       SET ORDER TO bar
       LOCATE FOR foo = "AAA"

       Then Rushmore will locate records in the following order:
       RECNO() 1
       RECNO() 2
       RECNO() 4
       RECNO() 3

       Of course, you shouldn't program with this in mind, because
       may change it in future releases.

5.3 Rushmore and the SET RELATION TO statement.
    Beware of attempting to use Rushmore along with SET RELATION.  Due
    to the constraints of the SET RELATION statement, Rushmore will
    either not work at all, or will only work in very limited cases.

5.4 Rushmore and the SET DELETED statement.
    Rushmore can optimize a query for deleted() IF you index on deleted()
    If your database contains a large number of deleted records, you
    can help Rushmore out by either indexing on deleted() with SET
    or by using SET DELETED OFF before you invoke Rushmore.