June 25, 2009 at 9:39 am
Say I have two tables, Employee and PayRate. Employee has a primary key
EmpID which is a foreign key to PayRate. I need to update any matched
employees whose pay rate is greater than 1. I have to consider both
performance and mutual lock. Here are two solutions.
1. SELECT then UPDATE
select @cnt=count(*) from employee
where name=@name and dob=@dob and
empid in (select empid from payrate where rate >1)
if @cnt > 1
begin
update employee set status = 1
where name=@name and dob=@dob and
empid in (select empid from payrate where rate > 1)
2. UPDATE directly
update employee set status = 1
where name=@name and dob=@dob and
empid in (select empid from payrate where rate > 1)
I know the 2nd has better performance than the 1st. How about locks? I don't
know if the 1st has better chance to reduce dead lock?
June 26, 2009 at 4:46 am
Exclusive locking applies only to modified rows. I suggest you UPDATE directly.
Regards
Gianluca
-- Gianluca Sartori
June 26, 2009 at 4:53 am
the first may give you more problems - since the total duration of the loop is potentially very long, you may update records that have been modified since you started the query - or even miss records that now fall into your criteria
avoid using loops/cursors
sql server has lock escalation for a reason - it decides how best to lock data and not us.
MVDBA
June 26, 2009 at 4:54 am
apologies - misread the question - saw the rowcount and assumed it was a while loop - ignore me - i need more coffee:-D
MVDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply