The LA Fox Developer Newsletter

September 1994

Hot Tips (Con’t from page 6)

records match “CO”. Foxpro’s internal index

search functions are extremely efficient, so it can

locate the “CO” records almost instantaneously

using the .CDX. file. All commands that accept a

FOR clause, as well as SQL commands with a

WHERE clause, benefit from Rushmore as long as

you have index tags on the fields mentioned in the

FOR or WHERE clause.

The real key to Rushmore’s speed is that it only

needs to look at the .CDX file to determine which

records match your FOR or WHERE clause - the

.DBF and .FPT files aren’t needed at all. Based on

the .CDX information, it builds in memory a list of

the matching record numbers in the .DBF. Only

when the search is complete does it go to the

.DBF and retrieve the actual records.

If you’re like me, you normally specify SET

DELETED ON as the default in your programs.

Otherwise, the user will see deleted records in

data entry and they will be included in queries and

reports. Unfortunately, SET DELETED ON

causes problems for Rushmore. When DELETED

is ON, the clause “FOR state = ‘CO’” in effect

becomes “FOR state = ‘CO’ AND NOT DE-

LETED() “. As we all know (or look at P. A-21 in

the Developer’s Guide!) the deleted mark for each

record is in the .DBF file, not in the .CDX file. As a

result, Rushmore can no longer use the .CDX

alone. Instead, it first selects all matching records

using the .CDX file, then must inspect each indi-

vidual record in the .DBF file in order to determine

which records are deleted. Even if there are no

deleted records, Rushmore doesn’t know this

without checking each record one by one.

The resulting slowdown can be significant. In

order to demonstrate this, I created a 5,000,000

record file (yes, million) with the following structure:

STATE

C2

CODE

Cl

AMOUNT

N6.2

populated it with random numbers, code letters,

and states, then created an index tag on each

field. (Do not attempt this yourself at home.) I

then made some comparisons with DELETED set

ON and OFF:

That’s certainly a dramatic difference, isn’t it?

So what can you do about this? Here are some

suggestions:

(1) Test your programs, or individual queries, with

DELETED ON and DELETED OFF and see if you

notice a difference. If it ain’t broke, maybe you don’t

want to fix it.

(2) If deleted records aren’t a problem in your

application, make sure to SET DELETED OFF before

any SQL queries, or any commands involving FOR.

(3) If neither of these works, there is a little trick

you can use to help Rushmore. For each data file to

be searched, include a DELETED() tag:

INDEX ON DELETEDO TAG <your tag name

here>

I usually use “DELETED” for the tag name, but it’s

irrelevant. The important thing is to index on the

DELETEDO function. It may come as a surprise, but

Rushmore actually checks for a DELETED() tag in the

.CDX file each time it is called. If the tag exists, then

Rushmore can use it to determine which records are

deleted, and the whole query can be done in the .CDX

file, even with SET DELETED ON.

To illustrate this, I added a DELETEDØ tag to my

.CDX and re-ran the test:

As you can see, the DELETEDO tag even things out.

There are a few downsides to using a DELETEDO tag.

First, it makes the .CDX file larger, of course. Second,

with SET DELETED OFF, it slows things down slightly

- apparently Rushmore uses the DELETED tag even

when it doesn’t need to. Finally, the DELETE com-

mand itself is slowed down slightly, since it must now

(Con’t, page 8)

Page 7