General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Network:
Windows 2003 Server
>>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
try to check the theory.
Into another datasession or application,
declare a RLOCK() for a record out of the deleted set.
Then run the slow DELETE and the Fast DELETE.
Any difference ?
Fabio
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only