记一次sql优化

客户反馈在销售订单下推生产订单时,耗时1mins左右。使用sql server profiler抓取sql,分析执行计划发现主要消耗如下:


初始sql执行计划

从执行计划中,找到对应的sql脚本如下:

-- 1排除服务物料或者不允许生产的物料
AND NOT EXISTS (
        SELECT 1
        FROM T_BD_MaterialBase MB
        INNER JOIN T_BD_MATERIAL MBTA ON MB.FMATERIALID = MBTA.FMATERIALID
        WHERE (
                (
                    (
                        MB.FErpClsID = '6'
                        OR FIsProduce = '0'
                        )
                    AND MBTA.FUSEORGID = t2.FSUPPLYORGID
                    )
                AND EXISTS (
                    SELECT 1
                    FROM T_BD_MATERIAL MTR
                    WHERE (
                            MTR.FMATERIALID = t2.FMATERIALID
                            AND MTR.FMASTERID = MBTA.FMASTERID
                            )
                    )
                )
        ) )
-- 2 排除收款计划中控制环节为生产且实收与应收不等(即收款计划中控制环节为生产且未收款完成的订单不允许生产)
    AND NOT EXISTS (
        SELECT 1
        FROM t_sal_orderPlan a
        INNER JOIN (
            SELECT sum(a.FRECADVANCEAMOUNT) frecadvanceamount
                ,sum(a.FRecAmount) frecamount
                ,a.FID
            FROM t_sal_OrderPlan a
            INNER JOIN t_sal_orderPlan b ON (
                    a.FID = b.FID
                    AND (a.FSeq <= b.FSeq)
                    )
            WHERE (
                    b.FControlSend = 'PRODUCT'
                    AND a.FID = b.FID
                    )
            GROUP BY a.FID
            ) b ON a.FID = b.FID
        WHERE (
                (b.Frecadvanceamount <> b.FRecAmount)
                AND a.FId = t0.FID
                )
        )

执行计划中显示【T_SAL_ORDERPLAN】表子查询为【聚集索引扫描】,针对输出列,在【T_SAL_ORDERPLAN】表中添加索引。再次执行,耗时还是1mins左右,查看执行计划,【聚集索引扫描】已经优化成【索引查找】,但优化效果不明显。

添加索引后

执行计划分析无果,只得对sql语句进行分析,捋一捋业务逻辑。
先来看一下第一段逻辑

-- 1排除服务物料或者不允许生产的物料
AND NOT EXISTS (
        SELECT 1
        FROM T_BD_MaterialBase MB
        INNER JOIN T_BD_MATERIAL MBTA ON MB.FMATERIALID = MBTA.FMATERIALID
        WHERE (
                (
                    (
                        MB.FErpClsID = '6'
                        OR FIsProduce = '0'
                        )
                    AND MBTA.FUSEORGID = t2.FSUPPLYORGID
                    )
                AND EXISTS (
                    SELECT 1
                    FROM T_BD_MATERIAL MTR
                    WHERE (
                            MTR.FMATERIALID = t2.FMATERIALID
                            AND MTR.FMASTERID = MBTA.FMASTERID
                            )
                    )
                )
        ) )

该段SQL目的是排除服务物料或者不允许生产的物料。反过来,就是只过滤显示【非服务且允许生产的物料】,改造SQL如下:

AND EXISTS (
        SELECT 1
        FROM T_BD_MATERIAL MBTA
        INNER JOIN T_BD_MaterialBase MB ON MB.FMATERIALID = MBTA.FMATERIALID
        WHERE MB.FErpClsID <> '6'
            AND MB.FIsProduce = '1'
            AND MBTA.FUSEORGID = FSupplyOrgId
            AND EXISTS (
                SELECT 1
                FROM T_BD_MATERIAL MTR
                WHERE (
                        MTR.FMATERIALID = t2.FMATERIALID
                        AND MTR.FMASTERID = MBTA.FMASTERID
                        )
                )
        )   
Not exists转换为exists

从执行计划上看,优化结果不明显。

寻求分析第二段代码:

-- 2 排除收款计划中控制环节为生产且实收与应收不等(即收款计划中控制环节为生产且未收款完成的订单不允许生产)
    AND NOT EXISTS (
        SELECT 1
        FROM t_sal_orderPlan a
        INNER JOIN (
            SELECT sum(a.FRECADVANCEAMOUNT) frecadvanceamount
                ,sum(a.FRecAmount) frecamount
                ,a.FID
            FROM t_sal_OrderPlan a
            INNER JOIN t_sal_orderPlan b ON (
                    a.FID = b.FID
                    AND (a.FSeq <= b.FSeq)
                    )
            WHERE (
                    b.FControlSend = 'PRODUCT'
                    AND a.FID = b.FID
                    )
            GROUP BY a.FID
            ) b ON a.FID = b.FID
        WHERE (
                (b.Frecadvanceamount <> b.FRecAmount)
                AND a.FId = t0.FID
                )
        )

仔细一看,这什么鬼,【t_sal_OrderPlan】表,与自身inner join了三次,相当于嵌套查询三次。优化为:

-- 排除收款计划中控制环节为生产且实收与应收不等(即收款计划中控制环节为生产且未收款完成的订单不允许生产)
AND NOT EXISTS (
        --SELECT 1
        --FROM t_sal_orderPlan a
        --INNER JOIN (
        SELECT a.FID
        --,sum(a.FRECADVANCEAMOUNT) frecadvanceamount
        --,sum(a.FRecAmount) frecamount
        FROM t_sal_OrderPlan a
        WHERE a.FControlSend = 'PRODUCT'
            AND a.fid = t0.fid
        GROUP BY a.FID
        HAVING sum(a.FRECADVANCEAMOUNT) <> sum(a.FRecAmount)
            --) b ON a.FID = b.FID
            --WHERE a.fid = t0.fid
        )

这一改不要紧,内部查询是快了,返回空,但是与外部关联嵌套查询,直接几分钟没出来结果。

只能寻求其他优化,分析逻辑大致是【排除收款计划中控制环节为生产且实收与应收不等(即收款计划中控制环节为生产且未收款完成的订单不允许生产)】。取反:

  1. 过滤显示收款计划中控制环节为非生产
  2. 过滤收款计划中控制环节为生产但收款完成

优化sql如下:

-- 1. 过滤显示收款计划中控制环节为非生产的
    AND EXISTS (
        SELECT 1
        FROM T_SAL_ORDERPLAN a
        WHERE a.FCONTROLSEND <> 'PRODUCT'
            AND a.fid = t0.fid
        
        UNION ALL
-- 2. 过滤收款计划中控制环节为**生产但收款完成**的      
        --SELECT 1 FROM t_sal_orderPlan c
        --              INNER JOIN (
        SELECT a.FID
        --,sum(a.FRECADVANCEAMOUNT) frecadvanceamount
        --,sum(a.FRecAmount) frecamount
        FROM t_sal_OrderPlan a
        WHERE a.FControlSend = 'PRODUCT'
            AND a.FID = t0.fid
        GROUP BY a.FID
        HAVING sum(a.FRECADVANCEAMOUNT) <= sum(a.FRecAmount)
            --) b ON c.FID = b.FID 
            --where c.fid = t0.fid              
        )

优化后,时间缩减为10s内,优化结果可以接受。优化后的执行计划为:


最终优化

针对这次优化,有以下疑问:

  1. Not exists与exists性能问题
  2. 优化后,执行计划报No join predicate

推荐阅读更多精彩内容