MS Sql 优化步骤及优化not in一例

A.cCrm, A.cCode AS cOrderCode, A.dRequire, A.dSubmit, B.*, C.cCode AS cProductCode, B.cProductSpec BcProductSpec, A.dConfirm, A.dCheck1, C.cParamter, C.cSpec AS cProductSpec, C.cColor, A.cCreator FROM Orders A WITH ( NOLOCK ) LEFT JOIN Orders_Product B WITH ( NOLOCK ) ON A.cID = B.cOrdersID LEFT JOIN Product C WITH ( NOLOCK ) ON B.cProductID = C.cID LEFT JOIN ( --生产的产品ID SELECT DISTINCT A1.cProductID FROM dbo.Product_Item A1 LEFT JOIN dbo.Orders_ProductItem A2 ON A1.cProductID = A2.cProductID WHERE A1.iProduct != 0 ) D ON B.cProductID = D.cProductID WHERE 1 = 1 AND B.cProductID = D.cProductID AND A.iCancel = ‘0‘ AND ( iStatus = 30 OR ( iStatus = 20 AND iNewCRM != 1 AND NOT EXISTS ( SELECT 1 FROM Orders_ProductItem WITH ( NOLOCK ) WHERE iCustom = 1 AND cOrdersID = A.cID ) ) ) AND ( A.iStatusPP = 0 OR A.iStatusPP = 1 ) AND NOT EXISTS ( SELECT 1 FROM MOrders_Product LEFT JOIN dbo.MOrders ON MOrders.cID = MOrders_Product.cMOrdersID WHERE cOrdersProductID = B.cSubID AND dbo.MOrders.iStatus != 2 ) AND B.iCancelM = 0 AND B.cSubID NOT IN ( SELECT B.cOrdersProductID FROM DOrders A LEFT JOIN DOrders_Sub B ON A.cID = B.cDOrdersID WHERE iStatus = 3 ) ORDER BY A.dUDate DESC;

经过分析定位到：not in 导至系统卡顿：

 

 B.cSubID NOT IN (
        SELECT B.cOrdersProductID
            FROM DOrders A
            LEFT JOIN DOrders_Sub B ON A.cID = B.cDOrdersID
            WHERE iStatus = 3

 

 将not in 改为 not exists问题得以解决，系统正常运作。

改后的代码为：

SELECT A.cCrm, A.cCode AS cOrderCode, A.dRequire, A.dSubmit, B.*,
        C.cCode AS cProductCode, B.cProductSpec BcProductSpec, A.dConfirm,
        A.dCheck1, C.cParamter, C.cSpec AS cProductSpec, C.cColor, A.cCreator
    FROM Orders A WITH ( NOLOCK )
    LEFT JOIN Orders_Product B WITH ( NOLOCK ) ON A.cID = B.cOrdersID
    LEFT JOIN Product C WITH ( NOLOCK ) ON B.cProductID = C.cID
    LEFT JOIN (
                            --生产的产品ID
                SELECT DISTINCT A1.cProductID
                    FROM dbo.Product_Item A1
                    LEFT JOIN dbo.Orders_ProductItem A2 ON A1.cProductID = A2.cProductID
                    WHERE A1.iProduct != 0
              ) D ON B.cProductID = D.cProductID
    WHERE 1 = 1
        AND B.cProductID = D.cProductID
        AND A.iCancel = ‘0‘
        AND ( iStatus = 30
              OR ( iStatus = 20
                   AND iNewCRM != 1
                   AND NOT EXISTS ( SELECT 1
                                        FROM Orders_ProductItem WITH ( NOLOCK )
                                        WHERE iCustom = 1
                                            AND cOrdersID = A.cID )
                 )
            )
        AND ( A.iStatusPP = 0
              OR A.iStatusPP = 1
            )
        AND NOT EXISTS ( SELECT 1
                            FROM MOrders_Product
                            LEFT JOIN dbo.MOrders ON MOrders.cID = MOrders_Product.cMOrdersID
                            WHERE cOrdersProductID = B.cSubID
                                AND dbo.MOrders.iStatus != 2 )
        AND B.iCancelM = 0
        AND NOT EXISTS (
        SELECT 1
            FROM DOrders AA
            LEFT JOIN DOrders_Sub BB ON AA.cID = BB.cDOrdersID
            WHERE iStatus = 3 AND B.cSubID=bb.cOrdersProductID)
    ORDER BY A.dUDate DESC; 

 

原因可以参考以下文章：

 https://www.cnblogs.com/totian/p/7597300.html

 

 

https://blog.csdn.net/zxu_1995/article/details/82388395

 

MS Sql 优化步骤及优化not in一例

标签：资源   creat   sub   mit   spec   数据库   任务管理   技术   tco