f*****e
发帖数: 5177 | 1 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
( |
gy
发帖数: 620 | 2 下面两句我没看出啥区别来, 谁说说??
(一个先判断一下, 然后update, 另一个直接update.)
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)
【在 f*****e 的大作中提到】
: 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
|
B*****g
发帖数: 34098 | 3 乱，看不懂。不看了，凑活用merge
in
【在 f*****e 的大作中提到】
: 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
|
j*****n
发帖数: 1781 | 4 use IF EXISTS instead of count(*)
in
【在 f*****e 的大作中提到】
: 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
|
z***y
发帖数: 7151 | 5 The first solution actually duplicated in select statement.
To minimize the lock time or avoiding lock escalation, make sure you have
non clustered index on rate column in payrate table.
What would happen if you don't have non clustered index on rate column is
that query optimizer would upgrade to table level share lock if it think it
was more efficient than row level share lock. When table level share lock
was applied to table, it would exclude any update or insert operations onto
this table.
【在 f*****e 的大作中提到】
: 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
|
z***y
发帖数: 7151 | 6 Actually in this senario, there are no difference. A friend Andy Lenard
wrote a CLR function to tell how it works internally, he found that sql
server will return true as soon as it found first matched record. In this
senario,
it would not fetch data before sending true or false.
Another way to verify this is from the execution plan.
【在 j*****n 的大作中提到】
: use IF EXISTS instead of count(*)
:
: in
|
