Hi Walter,

>When there are no deleted records, VFP has to read the records anyway because it has to process the record (E.g:copy them into the cursor), whether there is or is no tag on deleted().

No, not in any situation VFP has to read the record. Of course, in your test programs in does, that's why you theory seems to be proven by that programs. But in reality you don't always need a non-filtered table and you have multi-table joins.

>>1. How much time does this checking need compared with the time to create a bitmap? Here take into account that a bitmap has only to be generated once, while checking is performed every time.
>
>Could you explain this a little better ? a (index) bitmap ?

The Rushmore engine creates a bitmap for any index expression. That's a memory block where each bit represents a record. When a bit is 1, the record is part of the selection, when it's 0, the record is not part of the selection. This bitmap is created based on the index tree. In order to perform logical operations like AND or OR, all VFP has to do is to use bit oriented CPU commands to combine two bitmaps into one. Since that's done on the CPU level, it's quite fast.

Such a bitmap can be re-used until the cache times out, by default about 300 ms. After that, VFP recreates the bitmap if necessary.

>If the optimizer determines that there are no deleted records, it returns the whole table as a recordset, after which VFP has to read the actual records anyway. The would be NO speed improvement because the index didn't make the selection any smaller than the table itself.

No, VFP doesn't read all records in a result set. It does so only, when you tell it to do so. That means, when you access such a record.

>If you use the index on deleted() VFP has to read more info because it has to read the index and the actual table as well.

Right, in a single table situation when all records are physical read from the server, but wrong in any other situation. And the point here is delayed reading. When you read records as soon as you need them, you return earlier. You don't have to wait until everything has been read.

>As the OS reads just clusters it has to read the whole record. VFP cannot optimize queries by only reading parts of records. Only when you've very small clusters in combination with very wide tables it could be theorecticly done.

Nope, VFP reads bytes. How much actually is transferred across the network depends on the OS and the server. VFP just tells the OS, give me the next 20 bytes starting at position 123457. The OS then reads these bytes from the cache or sends a request to the server. It might read actually more bytes and cache them. The server probably reads the entire sector/cluster, and the HD reads the entire track. It really depends on the system what physically transfered, but VFP as the application is not limited to clusters, just like you are not limited to clusters when you use FOPEN(), FSEEK() and FREAD().

>What's the difference ? With the deleted record it has first to check that the record is not deleted and only after this it reads the record.

Depends on whether there are deleted record inbetween the current record and the target record. When there's a deleted record, without index VFP has to read all deleted records inbetween, when there's no deleted record, you should notice a significant difference.

Christof