On 23/11/11 15:16, Carl (CBM) wrote:
One more DB performance report/question. I am seeing
some UPDATE
queries that only change one row but take much, much longer than they
ought to. Is anyone else seeing this?
For example, the following type of query is getting killed from time to
time in the p_enwp10 database on sql-s1. The query killers says it ran
for over 400 seconds before being killed. The update is on a primary
key, and I don't see any way to optimize it. At the time this is
running the database connection is inside a transaction (AutoCommit = 0)
if that matters.
UPDATE tmpcategories
SET c_category = 'A-Class_Water_supply_and_sanitation_articles',
c_ranking = '425', c_replacement = 'A-Class'
WHERE c_project = 'Water_supply_and_sanitation' and c_rating= 'A-Class'
and c_type = 'quality'
- Carl
I guess you could make the query sorter by splitting it adding a LIMIT
clause, then rexecing while the affected rows = limit. (Assuming you
don't need the UPDATE to be atomic)