Cristof,

>>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...

I will ;-)

>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.

Remark: It doesn't really scan sequentially, it just looks for the following indexnodes which is in the same tag, downloads the actual record and checks if the filter applies. If not it searches for the next indexnode, etc.. IOW it searches the index sequentially rather than the table itself.

There is a general thinking that filtered tables with SEEK is slow. This is not entirely true. It can be slow if the filter's seleticvity is high (many records are filtered out). If a SEEK command only has to scan about a few nodes further because the others where filtered out, you'll like not have any performance problems. OTOH, if the seek command has to attempt more than about 20 - 100 times before it finds a matching record you might be better of with a LOCATE which is fully optimizable.


>>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.

You're reffering to the local buffers on the OS level. In general I don't think this matters. I've done some testing with a 100 records (or so) table, in which I run trough a loop a couple of thousand times

FOR nT = 1 TO 10000
    LOCATE FOR Field = Value    && Optimizable
ENDFOR

FOR nT = 1 TO 10000
    LOCATE FOR Field = Value  NOOP  && Not optimizable
ENDFOR

I remember that the second one was the winner by about 300%. My conclusion back then was that the overhead of rushmore was the cause of the difference. IOW rushmore pays with larger recordssets. I've got to admit that I didn't test this in exclusive mode or in a multiuser access, just single use shared access. Just try it, and I like to know what you've found.

Another matter is when the table is modified since the last time, you've accessed the table, because VFP has to reload the indexnodes from the actual table. In this case, the difference between the two approaches would be even more.

My personal opinion is that rushmore pays only when:
- Working with larger datasets and,
- in situations where you want to use more than 1 index to speed up performance (Especially in clauses where the OR operator is used).

In other situations you might be better off with either:
- disableling rushmore OR,
- SET the table ORDER, use a SEEK to find the first matching record and after that, use a WHILE clause to adress the rest of the records sequentially.

Walter,