项目中接收离职同事的统计部分代码的优化,客户反映随着时间查询越来越慢,最长的到了5分钟左右,客户当然反馈很强烈,系统太不稳定了,反映越来越慢。。。,不多说直接上sql代码
SELECT ajjbxx.ssbm, pqNum
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE 20 - ISNULL(gzNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) + round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE 20 - (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) + round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(aqNum, 0) * 40.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) + round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(clfkNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS df
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE 20 - ISNULL(gzNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS gzldf
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(gzNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS gzl
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE 20 - (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS fgldf
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE (ISNULL(fgNum, 0) + ISNULL(fgNum2, 0)) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS fgl
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(aqNum, 0) * 40.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS aqczldf
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(aqNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS aqczl
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(clfkNum, 0) * 20.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS czldf
, round(CASE
WHEN ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) = 0 THEN 1
ELSE ISNULL(clfkNum, 0) * 100.00 / (ISNULL(pqNum, 2) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0))
END, 2) AS czl
, ISNULL(pqNum, 0) - ISNULL(zfNum, 0) - ISNULL(jyNum, 0) AS yczNum
, zzjg.dwmc, clfkNum, htNum, aqNum, cqNum
, ISNULL(fgNum, 0) + ISNULL(fgNum2, 0) AS fgNum
, clzNum, zpqNum, zhtNum, zfNum, gzNum
, cqwclNum
FROM (
SELECT *
FROM t_ajjbxxb
) ajjbxx
LEFT JOIN (
SELECT *
FROM t_zzjgb
) zzjg
ON zzjg.zzbh = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS pqNum, a.ssbm
FROM (
SELECT ajbh, ssbm, lcbh, sbsj
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '344D8F3F-8FA8-403C-AEBD-C663008BFBAA'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
) a
GROUP BY ssbm
) pq
ON pq.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS zfNum, a.ssbm
FROM (
SELECT ajbh, ssbm, lcbh, sbsj
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = 'B9AB809C-87D5-41F9-9520-48980DFA289F'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
) a
GROUP BY ssbm
) zf
ON zf.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS gzNum, a.ssbm
FROM (
SELECT ajbh, ssbm, lcbh, sbsj
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '92D1D71F-274D-4423-8372-0B094DB50A16'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
) a
GROUP BY ssbm
) gz
ON gz.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS jyNum, a.ssbm
FROM (
SELECT ajbh, ssbm, lcbh, sbsj
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = 'A85A6190-B0DB-4C09-AB94-E386C29A1D74'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm, ajbh, ssbm, lcbh, sbsj
) a
GROUP BY ssbm
) jy
ON jy.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS htNum, ssbm
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '5BDE3C96-C04E-4779-9F78-2287318E2F95'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm
) ht
ON ht.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS zpqNum
FROM (
SELECT COUNT(1) AS zzpqNum, ajbh
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '344D8F3F-8FA8-403C-AEBD-C663008BFBAA'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ajbh
) pq1
) zpq
ON 1 = 1
LEFT JOIN (
SELECT COUNT(1) AS zhtNum
FROM (
SELECT COUNT(1) AS zzhtNum, ajbh
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '5BDE3C96-C04E-4779-9F78-2287318E2F95'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ajbh
) ht1
) zht
ON 1 = 1
LEFT JOIN (
SELECT COUNT(1) AS clfkNum, ssbm
FROM (
SELECT ssbm, ajbh
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm, ajbh
) a
GROUP BY ssbm
) clfk
ON clfk.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS clzNum, ssbm
FROM t_ajjbxxb WITH (NOLOCK)
WHERE ajzt <> 'CE5B6B41-7B67-4476-884A-8590418F4DB7'
AND ajzt <> '19486445-BD8E-49D6-B635-DDD4A8783F5F'
AND ajzt <> '71668A4F-DC41-48FB-B911-2FF8AB9C5C4E'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm
) clz
ON clz.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS aqNum, aq1.ssbm
FROM (
SELECT a.ajbh, a.ssbm
FROM (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
) a
LEFT JOIN (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
) b
ON a.ajbh = b.ajbh
WHERE a.ajbh = b.ajbh
AND a.blqx > b.fqsj
AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY a.ssbm, a.ajbh
EXCEPT
SELECT a.ajbh, a.ssbm
FROM (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
) a
LEFT JOIN (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
) b
ON a.ajbh = b.ajbh
WHERE a.ajbh = b.ajbh
AND a.blqx < b.fqsj
AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY a.ssbm, a.ajbh
) aq1
GROUP BY aq1.ssbm
) aq
ON aq.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS cqwclNum, ssbm
FROM t_ajjbxxb WITH (NOLOCK)
WHERE getdate() > blqx
AND ajzt <> 'CE5B6B41-7B67-4476-884A-8590418F4DB7'
AND ajzt <> '19486445-BD8E-49D6-B635-DDD4A8783F5F'
AND ajzt <> '71668A4F-DC41-48FB-B911-2FF8AB9C5C4E'
AND ajzt <> 'E48625AA-4859-41AF-9570-30FD0F721BF6'
AND ajzt <> '48A8DCC5-CF36-45B8-B575-D538C1D3C996'
AND ajzt <> '85404CD0-ADA1-458B-AEE7-AB2BABDE15FA'
AND ajzt <> '78F6852F-BFC0-4CEA-B0F1-223F48F7FCA1'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY ssbm
) cqwcl
ON cqwcl.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS cqNum, ssbm
FROM (
SELECT a.ajbh, a.ssbm
FROM (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '9ADFAB81-96A6-4F5A-8E18-3ECA98CD65F7'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
) a
LEFT JOIN (
SELECT *
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '45CDC555-B94D-4844-9B43-F2D20D87FE24'
AND ajzt <> 'ED2E7CD7-F65D-40CE-8766-5898C51B23ED'
) b
ON a.ajbh = b.ajbh
WHERE a.ajbh = b.ajbh
AND a.blqx < b.fqsj
AND a.sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
GROUP BY a.ssbm, a.ajbh
) aq1
GROUP BY aq1.ssbm
) cq
ON cq.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS fgNum, ssbm
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '2442A76F-BB18-431F-AA2C-F1EFEF5A38FE'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
AND sbsj < '20180126'
GROUP BY ssbm
) fg
ON fg.ssbm = ajjbxx.ssbm
LEFT JOIN (
SELECT COUNT(1) AS fgNum2, ajbmbh
FROM (
SELECT MAX(ajbh) AS ajbh, ajbmbh
FROM V_ajlcb WITH (NOLOCK)
WHERE lcbh = '2442A76F-BB18-431F-AA2C-F1EFEF5A38FE'
AND sbsj BETWEEN '2018/3/15 0:00:00' AND '2018/3/15 23:59:59'
AND sbsj >= '20180126'
GROUP BY ajbmbh, ajbh
) fgcs
GROUP BY ajbmbh
) fg2
ON fg2.ajbmbh = ajjbxx.ssbm
WHERE ajjbxx.ssbm <> ''
AND zzjg.dwmc <> ''
GROUP BY zpqNum, zhtNum, ajjbxx.ssbm, pqNum, zzjg.dwmc, clfkNum, htNum, aqNum, fgNum, fgNum2, clzNum, cqNum, zfNum, gzNum, jyNum, cqwclNum
ORDER BY yczNum DESC
这段sql主要是通过关联查询嵌套子查询,同时有分组和排序要求,关联的表里面有视图(视图有记录表t_ajlcb和基本表t_ajjbxxb组成,以及其他字典表,基础数据表),所有表的数量大概10个表左右,记录表(视图)数据大概有15万条,基本表有1万多条。
查询时间大概是50秒到5分钟之间,开始分析原因:
1、查看sql执行计划,发现视图里面的t_ajlcb和t_ajjbxxb的表都是全表搜索,每个查询t_ajlcb占到了4%,t_ajjbxxb占2%这样下来12个子查询或者关联查询效率就到了(4%+2%)*10=72%,其他的就是排序排序占的效率高一点。很明显影响效率的就是因为两个数据比较多的表未做索引,接下来需要给他们选择合适的索引。
2、先看看这两个表的查询的字段是什么,然后再确定查询条件,本例中两个表最终统计的ajbh,ssbm这两个字段,查询条件已时间段为主,因此我们选择给这两个表分别创建聚集索引,创建什么样的索引根据个人的理解和经验,当然有一些原则,例如下图:
本例中我们也是一个范围查询,因此我们选择聚集索引,创建索引过程以SQLSERVER2008R2 来说明,选择表,下拉找到索引,新建索引
添加要新增的索引列
3、接下来再看看执行计划结果,执行时间不到5秒
起作用了最大的开销已经没了,基本表的t_ajjbxxb开销为2%是因为还有其他排序、分组,以及还有*查询的操作,需要进行代码优化。通过索引建立让原来最慢的5分钟最后降到1-2秒。
这样的结果基本上达到要求,如果还想优化掉一些大的开销,对代码进行分析优化,比如关联的时候以数据量大的作为查询结果在进行关联数据量小的表;少用select * 查询。
总之,分析sql效率的时候先看看执行计划,查找开销最大的,一般情况下索引就可以解决主要的问题,剩余就是根据需要优化sql。