>If I Set Deleted to On, Rushmore uses the IX_DEL (Index on deleted) and takes 6 seconds. If I set deleted to Off, Rushmore is still fully optimized, but no longer uses IX_DEL, this takes 0,6 seconds to build the list.
>
>I understand the usage of IX_DEL, but why is the second run (set deleted off) 10 times faster?
>

This sounds like the problem of non-selective indexes. There was an article by Chris Probst about this in FoxPro Advisor in May, 1999.

Here's the gist of the idea. Rushmore optimizes by looking at indexes instead of actual data. When you have an index that exactly matches a filter condition, Rushmore reads the portion of the index that corresponds to the condition you've specified. It does this for each optimizable filter condition and combines the results to figure out which records it needs to read. Then it reads those records and goes through them one by one to apply any conditions that aren't optimizable.

When you have an index that's not very selective, reading the appropriate portion of the index can take much longer than reading the actual records. Here's an example.

Suppose you have a million records and 10% are deleted. If you SET DELETED ON and have an index on DELETED(), when you do a query, Rushmore has to read 90% of the data for that index tag. That's a lot of data to transfer.

If other conditions in the query cut the number of records down, it may be faster for VFP to read all those records and check their deleted bits sequentially.

Bottom line: remove the tag on DELETED() and you should see improved performance.

Tamar