Hi Walter,

>You can enhance performance even more by filtering the index (FOR Clause, like FOR !DELETED()) for records which you don't want to search on. This way, SEEK will be even faster.

I like to expand on this issue. Please correct me, if you disagree...

Keith, first of all, adding a filter on NOT DELETED() does make SEEK faster, but it renders the index unusable for Rushmore. If you use this field in an expression that would gain from Rushmore (LOCATE, SELECT-SQL), you must have two indexes, on with and one without filter on NOT DELETED(). Please bear in mind that this increases the time it takes to add a record or change this field in an existing record. Only with extensive testing you can find out whether or not such an additional increases overall performance.

The reason SEEK is faster with an filtered index when SET DELETED is ON, is that VFP uses the index to find the first record and than scan sequentially all records until it finds a non-deleted one. It doesn't use the index on DELETED() here, but reads every single record.

>However, if you table is less than let's say 100 records, your LOCATE will run the fastest without rushmore, so include the NOOPTIMIZE clause. The break even point, is depended on a number of factors, but a general rule of thumb I use is about 100 records.

Doesn't this depend on whether the table is opened in shared more or exclusive, and whether other computers can modify it or not? When you have a cursor with less than 100 records and you LOCATE a record in this cursor several times, than Rushmore helps in my experience, because it has the entire index image in memory and can quickly build the bitmap.

Christof