阅读目录
- 案例语句
- 分析手段
- 如何优化
- 招聘信息
今天分析OR的优化方式,主要是案例进行剖析,单纯的说这个优化有多明显我认为是完全没有意义的,任何东西,任何道理,没有数据说话,总是会让人质疑罢了
回到顶部
案例语句
某大型房地产公司，巡检日期2013-04-23，问题语句
View Code
1 SELECT SUM(num) AS Num ,
2 HZLevel
3 FROM ( SELECT ISNULL(SUM(1), 0) AS num ,
4 '三年内合作' AS HZLevel
5 FROM ( SELECT DISTINCT
6 Providerguid
7 FROM vp_Provider2UnitGrid a
8 LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
9 OR b.bfProviderGUID = a.Providerguid
10 )
11 WHERE DATEDIFF(year, b.SignDate, GETDATE()) <= 3
12 ) a
13 UNION ALL
14 SELECT ISNULL(SUM(1), 0) AS num ,
15 '三年前合作' AS HZLevel
16 FROM ( SELECT Providerguid ,
17 MAX(SignDate) AS m
18 FROM vp_Provider2UnitGrid a
19 LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
20 OR a.Providerguid = b.bfProviderGUID
21 )
22 GROUP BY Providerguid
23 ) a
24 WHERE DATEDIFF(year, m, GETDATE()) > 3
25 UNION ALL
26 SELECT ISNULL(SUM(1), 0) AS num ,
27 '无合作' AS HZLevel
28 FROM ( SELECT DISTINCT
29 Providerguid
30 FROM vp_Provider2UnitGrid
31 WHERE Providerguid NOT IN (
32 SELECT DISTINCT
33 YfProviderGUID
34 FROM cb_Contract
35 WHERE YfProviderGUID IS NOT NULL
36 UNION
37 SELECT DISTINCT
38 bfProviderGUID
39 FROM cb_Contract
40 WHERE bfProviderGUID IS NOT NULL )
41 ) a
42 ) a
43 GROUP BY HZLevel
44 UNION ALL
45 SELECT ISNULL(SUM(1), 0) AS num ,
46 '合计' AS HZLevel
47 FROM ( SELECT DISTINCT
48 providerguid ,
49 hzlevel
50 FROM vp_Provider2UnitGrid
51 ) a
52 ORDER BY HZLevel DESC
View Code
SQL Server 分析和编译时间:
CPU 时间 = 187 毫秒，占用时间 = 247 毫秒。
(4 行受影响)
表 'Worktable'。扫描计数 8，逻辑读取 823264 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'cb_Contract'。扫描计数 18，逻辑读取 15552 次，物理读取 2 次，预读 1738 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'p_Provider'。扫描计数 16，逻辑读取 548 次，物理读取 2 次，预读 111 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 29717 毫秒，占用时间 = 8419 毫秒。
警告: 聚合或其他 SET 操作消除了空值。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
执行时间为8.4S，IO也是非常高的,类似这种语句如何下手?其实方法很简单,优化的效果也是非常好的
分析手段
因为语句中有UNION ALL,所以不用开执行计划,直接把每个UNION ALL起来的语句分别执行,找出最慢的语句即可
1 SELECT ISNULL(SUM(1), 0) AS num ,
2 '三年内合作' AS HZLevel
3 FROM ( SELECT DISTINCT
4 Providerguid
5 FROM vp_Provider2UnitGrid a
6 LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
7 OR b.bfProviderGUID = a.Providerguid
8 )
9 WHERE DATEDIFF(year, b.SignDate, GETDATE()) <= 3
10 ) a
1 SELECT ISNULL(SUM(1), 0) AS num ,
2 '三年前合作' AS HZLevel
3 FROM ( SELECT Providerguid ,
4 MAX(SignDate) AS m
5 FROM vp_Provider2UnitGrid a
6 LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
7 OR a.Providerguid = b.bfProviderGUID
8 )
9 GROUP BY Providerguid
10 ) a
11 WHERE DATEDIFF(year, m, GETDATE()) > 3
where条件不符合SARG，这样导致无法用到索引
2, 使用了视图，导致取数不是最优的，看是不是能按基表取数
3,OR的影响,OR和NOT IN一样,也会导致索引用不上,其实最可恶的是,OR还可能导致执行计划不是最优
那么影响点在哪里? 请看下面
1 SELECT ISNULL(SUM(1), 0) AS num ,
2 '无合作' AS HZLevel
3 FROM ( SELECT DISTINCT
4 Providerguid
5 FROM vp_Provider2UnitGrid
6 WHERE Providerguid NOT IN (
7 SELECT DISTINCT
8 YfProviderGUID
9 FROM cb_Contract
10 WHERE YfProviderGUID IS NOT NULL
11 UNION
12 SELECT DISTINCT
13 bfProviderGUID
14 FROM cb_Contract
15 WHERE bfProviderGUID IS NOT NULL )
16 ) a
刚刚列出了有问题的SQL,没有这段,说明这段SQL是没问题的,确实也是如此,这段SQL也用到了同样的视图, where条件也不符合SARG，但是它的速度确非常快
那么我有理由最先怀疑，问题点就出在OR这里，那么我把OR这个条件注释掉，再来看整个SQL语句，发现速度变快了，那么只需要优化OR即可
回到顶部
如何优化
1 SELECT ISNULL(SUM(1), 0) AS num ,
2 '三年内合作' AS HZLevel
3 FROM ( SELECT DISTINCT
4 Providerguid
5 FROM vp_Provider2UnitGrid a
6 LEFT JOIN cb_Contract b ON ( b.YfProviderGUID = a.Providerguid
7 --OR b.bfProviderGUID = a.Providerguid
8 )
9 WHERE DATEDIFF(year, b.SignDate, GETDATE()) <= 3
10 UNION
11 SELECT DISTINCT
12 Providerguid
13 FROM vp_Provider2UnitGrid a
14 LEFT JOIN cb_Contract b ON ( b.bfProviderGUID = a.Providerguid
15 )
16 WHERE DATEDIFF(year, b.SignDate, GETDATE()) <= 3
17
18 ) a
19
20 UNION ALL
21 SELECT ISNULL(SUM(1), 0) AS num ,
22 '三年前合作' AS HZLevel
23 FROM ( SELECT Providerguid ,
24 MAX(SignDate) AS m
25 FROM vp_Provider2UnitGrid a
26 LEFT JOIN cb_contract b ON ( a.Providerguid = b.YfProviderGUID
27 --OR a.Providerguid = b.bfProviderGUID
28 )
29 GROUP BY Providerguid
30 UNION
31 SELECT Providerguid ,
32 MAX(SignDate) AS m
33 FROM vp_Provider2UnitGrid a
34 LEFT JOIN cb_contract b ON ( a.Providerguid = b.bfProviderGUID
35 )
36 GROUP BY Providerguid
37 ) a
(2 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'p_Provider'。扫描计数 4，逻辑读取 224 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'cb_Contract'。扫描计数 4，逻辑读取 6088 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
SQL Server 执行时间:
CPU 时间 = 63 毫秒，占用时间 = 85 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
只需要把OR改成UNION即可，简单看一下，优化前后的对比结果，可谓天壤之别
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
