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 DELETED() “. 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 individual 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:

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 command itself is slowed down slightly, since it must now

(Con’t, page 8)

Page 7