December 11, 2004 at 4:45 pm
Hi All,
I need to do an update on a large number of records in a table. The records to update are determined by the value of a 'groupID' column in the table. Records are updated at varying times during the day.
The problem I have is that one of these groups has gotten vary large, so the update query operates on a large number of records now. I believe this is causing locking/blocking problems. I figure that if I can break up the update into chunks, it will allow other updates on the same table to occur. In that same table, I have another column called 'rank'. This column has a unique value from 1 to N in it for every record in the group. There are over 5000 records in the large group. My new update query updates this large table in chunks now based on "rank>0 and rank<=500", "rank>500 and rank<=1000", etc. There are now 11 chunks. With this change, the query is executed 11 times but is twice as slow as the single query that updates all of the records in one shot. That's not bad but that brings me to my question:
Will putting an index on the column "rank" help this query perform better? The rank column changes frequently. After I do the above mentioned updates whether it be in chunks or all at once, I then re-order the rank column. What can I do to help performance now that "rank" will be in the where clause of my chunky version?
simplified example (old):
update scoring set totalpts=(select num_correct from results where results.userid=scoring.userid) where groupid=@group_id
simplified example (new and this would be in a counting loop):
while @i<=@num_reps
begin
update scoring set totalpts=(select num_correct from results where results.userid=scoring.userid) where groupid=@group_id and rank>(@i-1)*500 and rank<=(@i*500)
set @i=@i+1
end
Thanks in advance!
Larry
December 12, 2004 at 10:34 am
Hi Larry,
From looking at your UPDATE statement, your performance problem seems to be in the way you are updating the totalpts column in the scoring table. The UPDATE statement has to query the results table for each record in the scoring table that matches the UPDATE statement's WHERE clause criteria. For the group that has 5000 records, then that means 5000 queries on the results table.
Try this UPDATE statement instead:
update scoring
set totalpts= r.num_correct
from
scoring s inner join results r on s.userid = r.userid
where
s.groupid = @group_id and
s.rank > (@i-1)*500 and
s.rank <= (@i*500)
Regards,
JP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply