SQLServer 复杂存储过程并发优化（案例）

最新推荐文章于 2024-08-09 14:18:49 发布

薛定谔的DBA

最新推荐文章于 2024-08-09 14:18:49 发布

阅读量8.9k

点赞数 5

分类专栏： SQLServer 调优优化 SQLServer

本文链接：https://blog.csdn.net/kk185800961/article/details/51940905

版权

SQLServer 同时被 2 个专栏收录

269 篇文章 25 订阅

订阅专栏

SQLServer 调优优化

22 篇文章 7 订阅

订阅专栏

一个存储过程，几千行代码，内部有一个查询，关联使用了200多张表（其中有重复的表），并发线程执行，耗时15秒左右，结果返回一般几行记录。这个存储过程是系统中最耗时、最消耗性能的。今天突然想着得优化一下了！

取出存储过程内的查询，声明相关参数执行脚本。语句比较复杂，取出一小段简化案例，主要问题也在这段代码中：

SELECT *
FROM TA
LEFT JOIN TB ON TA.ID=TB.ID
LEFT JOIN TC ON TA.ID=TC.ID
LEFT JOIN TD ON TA.ID=TD.ID
LEFT JOIN TE ON TA.ID=TE.ID
--(此处省略50张表的做连接)
WHERE TB.TOTAL>0
OR TC.TOTAL>0
OR TD.TOTAL>0
OR TE.TOTAL>0
--(此处省略50张表的 “OR T?.TOTAL>0”)

查看执行计划，其中有这么一段，如图：

有操作符 Filter ，将87万行数据变成了1行！这点是可以优化的点，为什么87万行不早点过滤变成1行？导致 Filter 右边的操作中，87万的数据参与过很多表的关联，性能很不好！Fileter 就是筛选，右键属性，查看属性中OoutputList 中的Predicate，这就是过滤条件。

在sql语句中，找到这个条件：

			where	t1_0_2.Total > 0 
				or	t1_0_3.Total > 0 
				or	t1_0_4.Total > 0 
				or	t1_0_5.Total > 0 
				or	t1_0_6.Total > 0 
				or	t1_0_7.Total > 0 
				or	t1_0_8.Total > 0 
				or	t1_0_9.Total > 0 
				or	t1_0_10.Total > 0 
				or	t1_0_11.Total > 0 
				or	t1_0_12.Total > 0 
				or	t1_0_13.Total > 0 
				or	t1_0_14.Total > 0 
				or	t1_0_15.Total > 0 
				or	t1_0_16.Total > 0 
				or	t1_0_17.Total > 0 
				or	t1_0_18.Total > 0
				or	t1_0_19.Total > 0
				or	t1_0_20.Total > 0
				or	t1_0_21.Total > 0
				or	t1_0_22.Total > 0
				or	t1_0_23.Total > 0
				or	t1_0_24.Total > 0
				or	t1_0_25.Total > 0
				or	t1_0_26.Total > 0
				or	t1_0_27.Total > 0
				or	t1_0_28.Total > 0
				or	t1_0_29.Total > 0
				or	t1_0_30.Total > 0
				or	t1_0_31.Total > 0
				or	t1_0_32.Total > 0
				or	t1_0_33.Total > 0
				or	t1_0_34.Total > 0
				or	t1_0_35.Total > 0
				or	t1_0_36.Total > 0
				or	t1_0_37.Total > 0
				or	t1_0_38.Total > 0
				or	t1_0_39.Total > 0
				or	t1_0_40.Total > 0
				or	t1_0_41.Total > 0
				or	t1_0_42.Total > 0
				or	t1_0_43.Total > 0
				or	t1_0_44.Total > 0
				or	t1_0_45.Total > 0
				or	t1_0_46.Total > 0
				or	t1_0_47.Total > 0
				or	t1_0_48.Total > 0
				or	t1_0_49.Total > 0
				or	t1_0_50.Total > 0
				or	t1_0_51.Total > 0
				or	t1_0_52.Total > 0

就是这个条件，这50多张表都计算一个total值，只要有一个大于0即可成立。or 在查询中尽量不用或者少用，可用其他方法替代，or 常常导致不能正确分析生成好的执行计划。

现在把条件等价改为如下：

			where	(t1_0_2.Total + 
				 t1_0_3.Total + 
				 t1_0_4.Total + 
				 t1_0_5.Total + 
				 t1_0_6.Total + 
				 t1_0_7.Total + 
				 t1_0_8.Total + 
				 t1_0_9.Total + 
				 t1_0_10.Total + 
				 t1_0_11.Total + 
				 t1_0_12.Total + 
				 t1_0_13.Total + 
				 t1_0_14.Total + 
				 t1_0_15.Total + 
				 t1_0_16.Total + 
				 t1_0_17.Total + 
				 t1_0_18.Total +
				 t1_0_19.Total +
				 t1_0_20.Total +
				 t1_0_21.Total +
				 t1_0_22.Total +
				 t1_0_23.Total +
				 t1_0_24.Total +
				 t1_0_25.Total +
				 t1_0_26.Total +
				 t1_0_27.Total +
				 t1_0_28.Total +
				 t1_0_29.Total +
				 t1_0_30.Total +
				 t1_0_31.Total +
				 t1_0_32.Total +
				 t1_0_33.Total +
				 t1_0_34.Total +
				 t1_0_35.Total +
				 t1_0_36.Total +
				 t1_0_37.Total +
				 t1_0_38.Total +
				 t1_0_39.Total +
				 t1_0_40.Total +
				 t1_0_41.Total +
				 t1_0_42.Total +
				 t1_0_43.Total +
				 t1_0_44.Total +
				 t1_0_45.Total +
				 t1_0_46.Total +
				 t1_0_47.Total +
				 t1_0_48.Total +
				 t1_0_49.Total +
				 t1_0_50.Total +
				 t1_0_51.Total +
				 t1_0_52.Total )>0

改了之后，在执行。现在变成单线程了！但还是花6秒左右。接下来再从新的执行计划中，看看哪张表或步骤开销最大，再另作优化。我这里修改了一张表的索引就行了，查询变成1秒这样。

但是，问题来了！！空值（null）加上数值还是一个空值（null）。所以，结果不准确！如果都改成这种判断：

isnull(t1_0_50.Total,0)>0

结果又与原来一样慢了！仔细查看业务和结果集不难发现，每次结果其实不会太多，除了左连接的第一张表87万行，其他表的记录只有几行或0行。左表与其他表连接后每张表的字段都变成87万行了！这样每一行都进行 or 判断将非常久。

所以考虑先过滤左表第一张表。进行左连接的其他表，都汇总计算出所有的连接键，与左表做inner join 连接，这样结果就更少了，可能只有几行或者0行了！左表一下子就变少了！类似改造如下：

SELECT *
FROM TA
INNER JOIN(
	SELECT ID FROM TB
	UNION
	SELECT ID FROM TC
	UNION
	SELECT ID FROM TD
	UNION
	SELECT ID FROM TE
	--(此处省略50张表的UNION连接)
) T ON TA.ID=T.ID
LEFT JOIN TB ON TA.ID=TB.ID
LEFT JOIN TC ON TA.ID=TC.ID
LEFT JOIN TD ON TA.ID=TD.ID
LEFT JOIN TE ON TA.ID=TE.ID
--(此处省略50张表的左连接)
WHERE TB.TOTAL>0
OR TC.TOTAL>0
OR TD.TOTAL>0
OR TE.TOTAL>0
--(此处省略50张表的 “OR T?.TOTAL>0”)

改造后，结果1秒钟！