优化更新语句中的标量子查询

9/1/2015来源:SQL技巧人气:1151

优化更新语句中的标量子查询

数据库环境: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

再贴上结果图,对比前面的子查询更新的图,是不是用内连接只更新了条件匹配的记录?

ipN8fEoZ4z9mYohxPWriOC7LURSpwtvbG+eCUtap2yJUN2sq9ze+3++FEHEcPz6lnIuRxgkhA95yY7y0LKXcbDZKd5ZlnIsgCDp1WzwOjvHvVTgXXIjNZsO5WK8f1MQY/94bQkifgonapw0xvl4/7Pf7OI6jKmqv1HnJbrwprtV4Z7nV+KlKM48/PqV5sAvBhTgej6cuCCF6QX+tLWBasWSg8bKsxzjnIssylS7X6weT8Zo4U/wOjvGm8ZeXlyRJkiQRQj4+pY9Pqb7NlLI+0u0eLVvrwHhrVnl7e9tsNuXZyW7cbm3M8mEYfqvCuTgej5UDUQi1G5IzzkUcx0r6tzYIIXq5SXMx0+qKIcZXq5UQQu1AmSLVa5krOeedWUV5bI1fS8jYwzwMw6RKM6vkxqWUUh6PR8oYpYxSlhgghNQKrW+ZqK0y0Ph567lQp8osy9brB3GS6shdrVaWtkrtH9Ba1ufor3ZMxtXBlySJOBXGOZOMSSkZp5wLyozGdb8moU2zpvljjatMUubuMlF2Gtdj3GScNFo1ndLDMPxapTSudItTJStmWXY8HrkQlLKvXRBCTHMsMV5bZaDxsmWiZxLVRFkVmIzX0kJrumguY1q9afy1ivJb6haiMM6YZPTl5YVxqoy/miGEqNcmpoVNDDQeBAGlrPaqFxSmT6j9A1rLpmXsdBgXkglatrWSJNnv91