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

来源：互联网发布：域名注册申请编辑：程序博客网时间：2024/05/20 20:05

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

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

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

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

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

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

wheret1_0_2.Total > 0 ort1_0_3.Total > 0 ort1_0_4.Total > 0 ort1_0_5.Total > 0 ort1_0_6.Total > 0 ort1_0_7.Total > 0 ort1_0_8.Total > 0 ort1_0_9.Total > 0 ort1_0_10.Total > 0 ort1_0_11.Total > 0 ort1_0_12.Total > 0 ort1_0_13.Total > 0 ort1_0_14.Total > 0 ort1_0_15.Total > 0 ort1_0_16.Total > 0 ort1_0_17.Total > 0 ort1_0_18.Total > 0ort1_0_19.Total > 0ort1_0_20.Total > 0ort1_0_21.Total > 0ort1_0_22.Total > 0ort1_0_23.Total > 0ort1_0_24.Total > 0ort1_0_25.Total > 0ort1_0_26.Total > 0ort1_0_27.Total > 0ort1_0_28.Total > 0ort1_0_29.Total > 0ort1_0_30.Total > 0ort1_0_31.Total > 0ort1_0_32.Total > 0ort1_0_33.Total > 0ort1_0_34.Total > 0ort1_0_35.Total > 0ort1_0_36.Total > 0ort1_0_37.Total > 0ort1_0_38.Total > 0ort1_0_39.Total > 0ort1_0_40.Total > 0ort1_0_41.Total > 0ort1_0_42.Total > 0ort1_0_43.Total > 0ort1_0_44.Total > 0ort1_0_45.Total > 0ort1_0_46.Total > 0ort1_0_47.Total > 0ort1_0_48.Total > 0ort1_0_49.Total > 0ort1_0_50.Total > 0ort1_0_51.Total > 0ort1_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 TAINNER JOIN(SELECT ID FROM TBUNIONSELECT ID FROM TCUNIONSELECT ID FROM TDUNIONSELECT ID FROM TE--(此处省略50张表的UNION连接)) T ON TA.ID=T.IDLEFT JOIN TB ON TA.ID=TB.IDLEFT JOIN TC ON TA.ID=TC.IDLEFT JOIN TD ON TA.ID=TD.IDLEFT JOIN TE ON TA.ID=TE.ID--(此处省略50张表的左连接)WHERE TB.TOTAL>0OR TC.TOTAL>0OR TD.TOTAL>0OR TE.TOTAL>0--(此处省略50张表的 “OR T?.TOTAL>0”)

改造后，结果1秒钟！

0 0