你的位置:首页 > 数据库

[数据库]优化有标量子查询的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    saft04
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' )
              OR '0100030' = 'MANAGER'
            )--904
SELECT COUNT(*) FROM  v_saft04_fexp  --1262584
SELECT COUNT(*) FROM  v_fadj_rd d --37077
SELECT COUNT(*) FROM  v_ctlm60 --431
SELECT 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,就不改了。