Cristof,


>>You can always use:
>>- USE Table
>>- SET FILTER TO Expr

>I'm talking about SQL-SELECTs here. Rushmore doesn't work with xBase commands across many tables, SQL does.

A multitable SQL-SELECT command would not generate a filtered table. All SQL Command which generate a filtered result can be substituted by the former example, (which is faster than a SQL command).

>>This depends on the number of deleted records in both tables. If there are none, VFP has to read the records anyway. If there are substancial amounts of records, i agree, the performance can be tuned up with an index on deleted()

>It does NOT! VFP does NOT read A SINGLE record from table B when you have an index on DELETED(), but it has to read ALL records when you don't have one. Please note, that in the result set there's no field from table B, thus, there's no need for VFP to read the record. All it does is to read the index of table B.

Though in theory you could be right, but as i have stated before: To my best knowledge VFP does not use full index scans (or was it fulll index coverage ?). This would mean that VFP reads the actual records in the latter table anyway.

I've tried your example with two tables where the b. table was about 150.000 records and could not find any significant difference whether SET DELETE was ON or OFF and/or where an index on DELETED exist or not.

Besides that, The example you showed is not quite common. In the majoraty of cases you would include one or more fields from table B. in the fields list or other parts of the SQL statement.

Maybe you can put an example together where you prove that VFP does not read the records (in a simular way David F did).

>>I'll hope you will discover by reading all the thread which i did participate in regarding this subject, i'm actually right.

>I read the entire thread, that's why I jump in. *s* Yes, you are right... in those cases that you showed in testcases, but not in all others. That it's faster to have no index on DELETED() when you have a single table SELECT with the NOFILTER option, doesn't say that you should always avoid an index. It really depends on the application and the kind of queries you run. That's my point, nothing more.

I'm aware that there are cases (for example: if it includes many deleted records) which could take advantage of a deleted tag on deleted(), but i really think that in an average appliation there would be no advantage by adding a deleted tag. If you can prove that your multitable does indeed not read the actual records in table B, then you would have another case in which an index on deleted could have some benefit (the actual gain of performance depends on a number of things like the number of records and the selectivity of the index key on ID and field). But until then i can only conclude that in general there is no gain of performance by adding an index tag on deleted().

Only in some cases (which the developper has to identify) an index on deleted() could be of any help.

Walter,