优化有标量子查询的SQL

数据库环境：SQL SERVER 2008R2

今天在数据库中抓出一条比较耗费资源的SQL，只返回904条数据，居然跑了40多分钟。SQL及对应的数据量如下图：

SELECT  saft04.cur_year ,        LEFT(saft04.dept_id, 4) sdept_id ,        saft04.vdept_id ,        saft04.dept_id ,        saft04.fee_id ,        saft04.vitem_id ,        ISNULL(saft04.fee_amt, 0) AS saft04_fee_amt ,        ISNULL(saft04.fee_qty, 0) AS saft04_fee_qty ,        ISNULL(saft04.fee_amt_flex, 0) AS saft04_fee_amt_flex ,        ISNULL(saft04.adj_amt, 0) AS saft04_adj_amt ,        ISNULL(saft04.init_amt, 0) AS saft04_init_amt ,        ISNULL(saft04.flex_amt, 0) AS saft04_flex_amt ,        ISNULL(saft04.conf_fee_amt, 0) AS saft04_conf_fee_amt ,        saft04.fc_app_no ,        saft04.zone_id ,        saft04.corr_id ,        CASE WHEN saft04.fc_app_no < '2010'             THEN ( CASE WHEN saft04.flexfapp_flag = 'Y'                         THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)                              - ISNULL(conf_fee_amt, 0)                         ELSE ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)                              + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt, 0)                    END )             ELSE CASE WHEN b.fee_type2 = '01'                            OR b.fee_type2 = '02'                       THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)                            - ISNULL(conf_fee_amt, 0)                       WHEN b.fee_type2 = '03'                       THEN ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)                            + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt, 0)                  END        END bal_amt ,        ISNULL(( SELECT SUM(b.oPR_amt)                 FROM   v_saft04_fexp b                 WHERE  b.fcapp_id = saft04.fc_app_no               ), 0) AS qty1 ,        CASE WHEN b.fee_type2 = '01'                  OR b.fee_type2 = '03'             THEN ISNULL(saft04.conf_fee_amt, 0)                  - ( ISNULL(( SELECT   SUM(b.opr_amt)                               FROM     v_saft04_fexp b                               WHERE    b.fcapp_id = saft04.fc_app_no                             ), 0) )             WHEN b.fee_type2 = '02'             THEN ISNULL(saft04.init_amt, 0) + ISNULL(saft04.flex_amt, 0)                  - ISNULL(( SELECT SUM(b.opr_amt)                             FROM   v_saft04_fexp b                             WHERE  b.fcapp_id = saft04.fc_app_no                           ), 0)                  + ISNULL(( SELECT SUM(d.opr_amt)                             FROM   v_fadj_rd d                             WHERE  d.fcapp_id = saft04.fc_app_no                           ), 0)        END qty2 ,        c.base_data2FROM    saft04        LEFT JOIN v_ctlm60 b ON b.fee_id = saft04.fee_id        LEFT JOIN ctlm1000 c ON c.d_type = 'fee_type2'                                AND b.fee_type2 = c.base_data1WHERE   1 = 1        AND saft04.com_id = 'LQPJ'        AND saft04.cur_year = 2015        AND saft04.dept_id LIKE '2001%'        AND ( saft04.dept_id IN ( SELECT    dept_id                                  FROM      ctlm2000                                  WHERE     user_id1 = '0100030' )              OR '0100030' = 'MANAGER'            )ORDER BY saft04.cur_year ,        saft04.vdept_id ,        saft04.dept_id ,        saft04.fee_id ,        saft04.vitem_id ,        saft04.zone_id ,        saft04.corr_id ,        saft04.fc_app_no-------------------------数据量统计----------------------------------SELECT COUNT(*)FROM saft04WHERE 1 = 1 AND saft04.com_id = 'LQPJ' AND saft04.cur_year = 2015 AND saft04.dept_id LIKE '%2001%' AND ( saft04.dept_id IN ( SELECT dept_id FROM ctlm2000 WHERE user_id1 = '0100030' ) OR '0100030' = 'MANAGER' )--904SELECT COUNT(*) FROM v_saft04_fexp --1262584SELECT COUNT(*) FROM v_fadj_rd d --37077SELECT COUNT(*) FROM v_ctlm60 --431SELECT COUNT(*) FROM ctlm1000 --377

看了一下SQL，有可能出现问题的地方有2个地方，第一个是saft04 表的过滤条件“saft04.dept_id LIKE '%2001%'”使用了模糊查询，导致

走不了既定的索引。经和业务员确定，最开始只是想查询以“2001”开头的单位，因此，这个条件改成“saft04.dept_id LIKE '2001%'”即可。

第二个问题，是最要命的，标量部分“SELECT SUM(b.opr_amt) FROM v_saft04_fexp b WHERE b.fcapp_id = saft04.fc_app_no”走

的执行计划是嵌套循环，因而要改成左联接。

改写后的SQL如下，只执行了23S就全部出结果了。

WITH    x0          AS ( SELECT   b.fcapp_id ,                        SUM(b.opr_amt) opr_amt               FROM     v_saft04_fexp b               GROUP BY b.fcapp_id             )    SELECT  saft04.cur_year ,            LEFT(saft04.dept_id, 4) sdept_id ,            saft04.vdept_id ,            saft04.dept_id ,            saft04.fee_id ,            saft04.vitem_id ,            ISNULL(saft04.fee_amt, 0) AS saft04_fee_amt ,            ISNULL(saft04.fee_qty, 0) AS saft04_fee_qty ,            ISNULL(saft04.fee_amt_flex, 0) AS saft04_fee_amt_flex ,            ISNULL(saft04.adj_amt, 0) AS saft04_adj_amt ,            ISNULL(saft04.init_amt, 0) AS saft04_init_amt ,            ISNULL(saft04.flex_amt, 0) AS saft04_flex_amt ,            ISNULL(saft04.conf_fee_amt, 0) AS saft04_conf_fee_amt ,            saft04.fc_app_no ,            saft04.zone_id ,            saft04.corr_id ,            CASE WHEN saft04.fc_app_no < '2010'                 THEN ( CASE WHEN saft04.flexfapp_flag = 'Y'                             THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)                                  - ISNULL(conf_fee_amt, 0)                             ELSE ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)                                  + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt,                                                              0)                        END )                 ELSE CASE WHEN b.fee_type2 = '01'                                OR b.fee_type2 = '02'                           THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)                                - ISNULL(conf_fee_amt, 0)                           WHEN b.fee_type2 = '03'                           THEN ISNULL(init_amt, 0) + ISNULL(flex_amt, 0)                                + ISNULL(adj_amt, 0) - ISNULL(conf_fee_amt, 0)                      END            END bal_amt ,            ISNULL(( x0.opr_amt ), 0) AS qty1 ,            CASE WHEN b.fee_type2 = '01'                      OR b.fee_type2 = '03'                 THEN ISNULL(saft04.conf_fee_amt, 0) - ( ISNULL(( x0.opr_amt ),                                                              0) )                 WHEN b.fee_type2 = '02'                 THEN ISNULL(saft04.init_amt, 0) + ISNULL(saft04.flex_amt, 0)                      - ISNULL(( x0.opr_amt ), 0)                      + ISNULL(( SELECT SUM(d.opr_amt)                                 FROM   v_fadj_rd d                                 WHERE  d.fcapp_id = saft04.fc_app_no                               ), 0)            END qty2 ,            c.base_data2    FROM    saft04            LEFT JOIN v_ctlm60 b ON b.fee_id = saft04.fee_id            LEFT JOIN ctlm1000 c ON c.d_type = 'fee_type2'                                    AND b.fee_type2 = c.base_data1            LEFT JOIN x0 ON x0.fcapp_id = saft04.fc_app_no    WHERE   1 = 1            AND saft04.com_id = 'LQPJ'            AND saft04.cur_year = 2015            AND saft04.dept_id LIKE '2001%'            AND saft04.dept_id IN ( SELECT  dept_id                                    FROM    ctlm2000                                    WHERE   user_id1 = '0100030' )    ORDER BY saft04.cur_year ,            saft04.vdept_id ,            saft04.dept_id ,            saft04.fee_id ,            saft04.vitem_id ,            saft04.zone_id ,            saft04.corr_id ,            saft04.fc_app_no

改写后的SQL还有一个标量子查询没处理，改写的思路和上面一样，因执行时间已经缩短到23S，就不改了。