数据库环境：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 < ''
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 = ''
OR b.fee_type2 = ''
THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)
- ISNULL(conf_fee_amt, 0)
WHEN b.fee_type2 = ''
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 = ''
OR b.fee_type2 = ''
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 = ''
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_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
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 = '' )
OR '' = '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 < ''
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 = ''
OR b.fee_type2 = ''
THEN ISNULL(fee_amt, 0) + ISNULL(adj_amt, 0)
- ISNULL(conf_fee_amt, 0)
WHEN b.fee_type2 = ''
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 = ''
OR b.fee_type2 = ''
THEN ISNULL(saft04.conf_fee_amt, 0) - ( ISNULL(( x0.opr_amt ),
0) )
WHEN b.fee_type2 = ''
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 = '' )
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，就不改了。