>VFP is known to do wonders with performance provided that it can "Rushmorize". And you would think that in my case all the tags are present for the Rushmore to kick in... What the heck is different between my situation and the "simple" delete that works fast even on large tables?
>
>Here is my theory so far (no more than speculation really):
>Rushmore needs to get the scope of records being affected, then change them. In my case it'd have to do it twice: 1st to get the Select part into an intermediate cursor and then 2nd time to join it with the table being updated. That does seem to be a "2nd join" problem affecting all SELECTs also and the reason for the FORCE clause in it.
>If I substitute "DELETE Resource FROM..." with "SELECT * FROM..." I get result fast even though SYS(3054) still shows no optimization on the Resource table.
>But for the Select it only needs to *read* the records and it can do it fast even without rushmorizing because it's VFP and because the table is not really big in size - just 2 integer fields and about 50K records.
>On the other hand, when it does Delete or Update, it still needs to process all the records *one by one* because of the "2nd join" problem. Only this time the "processing" involves these steps:
>1. RLOCK the record
>2. Read it
>3. See if it matches the Join condition
>4. Make update (or delete) if necessary
>5. Release the lock
>
Andrew,
If your conclusion is correct than I would consider this behavior a bug. The DELETE command shouldn't look records it's not going to delete.
--sb--