你的位置:首页 > 数据库

[数据库]优化更新语句中的标量子查询


数据库环境: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实现了还不够,还要考虑我们的代码会不会造成性能问题,再多想想可能的实现方法。

(本文完)