>>I have 2 VFP3 tables that I want to perform a SQL join on.
>>
>>Both tables have a regular index tag expression of DELETED().
>>
>>However, when SET DELETED is ON, the join takes 16 seconds to perform.
>>
>>When SET DELETED is OFF the JOIN takes less than 1 second.
>>
>>How can I speed up the join with DELETED ON?
>>
>>In VFP5 with =SYS(3054, 1), SQL ShowPlan says that both table's optimization level is FULL and that it's using the DELETED tag in both tables to "Rushmore Optimize" each table.
>>
>>Yet, with SET DELETE ON, the query takes 16+ seconds, with SET DELETE OFF, the query takes less than a second.
>>
>>How can I speed this up?
>>
>>Thanks,
>>
>>Jerryt
>
>How many records and for what purpose do you select?

It depends, sometimes between 4 - 40 records are selected, sometimes none.

My select is as follows;

SELECT mytable1.field1, mytable2.field2 ;
from mytable2, mytable2 ;
WHERE mytable1.field1 = "somevalue" ;
AND mytable1.keyfield = mytable2.keyfield ;
INTO CURSOR mycursor

Again, with DELETE OFF, this takes under 1 second, with DELETE ON, this takes 16+ seconds.

The "keyfield(s)" in both tables have regular index tags and both tables have a tag on DELETED().

Thanks,

Jerryt