>But MS SQL and Oracle have a large advantage:
>they files are in exclusive modality!!
>VFP is practically a system distributed that it shares files,
>distributed on separated pc;
>to guarantee performances and correctness is a terrible task (I says impossible).

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

Now, *that* is going to be slow on 50K records, but it explains why FLOCK helps. Why wouldn't they RLOCK only if necessary?

And if that's the case, shouldn't we avoid using that expanded UPDATEs and DELETEs syntax?

>You have try it with SET TABLEVALIDATE TO 0 ?

Didn't help. But nice try :)

--Andrew