数据库环境:SQL SERVER 2008R2
今天看到开发写的一条更新语句,第一眼是觉得这个SQL的业务有问题,再细看子查询部分,才意识到这是开发人员使的“怪招”。
这个SQL能满足业务的需要,只是开发人员在写这个SQL的时候应该不会考虑到存在性能问题。具体SQL如下:
UPDATE fapply_04SET conf_y_fee_amt = ISNULL(conf_y_fee_amt, 0) + ISNULL(( SELECT SUM(fexp_03.opr_amt) FROM fexp_03 WHERE fexp_03.com_id = fapply_04.com_id AND fexp_03.origin_no = fapply_04.fapply_no AND fexp_03.origin_line_no = fapply_04.line_no AND fexp_03.feetype_flag = '' AND fexp_03.fexp_no = :fexp_no ), 0)
开发人员原本想把fexp_03表中的opr_amt累加到对应记录的fapply_04表的conf_y_fee_amt字段上,但是,这条SQL实际上
把fapply_04整张表都更新了(没有对应记录则累加0)。
现在我们来做个实验,验证一下我的说法
1.数据准备
创建2张表,分别是表a和表b,脚本脚本如下:
--创建a表SELECT * INTO a FROM (SELECT 1 AS id,10 AS scoreUNION ALLSELECT 2 AS id,20 AS scoreUNION ALLSELECT 3 AS id,30 AS scoreUNION ALLSELECT 4 AS id,40 AS score) t--创建b表SELECT * INTO b FROM(SELECT 2 AS id,-20 AS cn) t
2.更新数据
如果a表和b表的id匹配,则累加b表对应的cn字段的数据,否则,减5。
UPDATE aSET score = score + ISNULL(( SELECT cn FROM b WHERE b.id = a.id ), -5)SELECT *FROM a
好,我们现在来对比一下更新前后,a表数据的变化。左图是更新前,右图是更新后。
看到没,是不是a表发生了全表更新呢?
全表更新把不必要的记录也更新了,数据量大的时候,会造成不必要的影响。
那怎么避免这个问题呢?
把子查询改成内连接即可,看脚本
UPDATE aSET a.score = a.score + b.cnFROM a INNER JOIN b ON a.id = b.idSELECT *FROM a
再贴上结果图,对比前面的子查询更新的图,是不是用内连接只更新了条件匹配的记录?
小结一下,有些时候,我们光是把SQL实现了还不够,还要考虑我们的代码会不会造成性能问题,再多想想可能的实现方法。
(本文完)
原标题:优化更新语句中的标量子查询
关键词: