SQL優化實例：從運行30分鐘到運行只要30秒

2024-07-21 02:31:51

字體：大中小

來源：轉載

供稿：網友

以下的SQL語句在服務器需要運行長達30分鐘才能完成:
SELECT     dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode,
                      dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity * dbo.Commodity.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy,
                      dbo.ComFlow.FlowDate) + '-' + DATENAME(mm, dbo.ComFlow.FlowDate) + '-' + DATENAME(dd, dbo.ComFlow.FlowDate)) AS FlowDate,
                      dbo.ComFlow.SalType, dbo.Employee.DepartCode AS DepartIn, dbo.Sale.DepartCode AS DepartOut,
                      dbo.ComFlow.Quantity * dbo.Commodity.TradePRice * dbo.Commodity.Discount / 100 AS Total, dbo.Department.GrpCode AS GrpCodeIn,
                      Department1.GrpCode AS GrpCodeOut
FROM         dbo.ComFlow INNER JOIN
                      dbo.Customer ON
                dbo.ComFlow.SalType IN (N'促銷', N'流向退貨', N'多級流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode
                Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退貨') AND ComFlow_1.OutCustCode = Customer_1.CustCode
            INNER JOIN
                      dbo.CustomerRelation ON dbo.ComFlow.ComCode = dbo.CustomerRelation.ComCode AND
                      dbo.CustomerRelation.CustCode = dbo.Customer.CustCode INNER JOIN
                      dbo.Employee ON dbo.CustomerRelation.EmpCode = dbo.Employee.EmpCode INNER JOIN
                      dbo.Sale ON dbo.ComFlow.SaleCode = dbo.Sale.SaleCode INNER JOIN
                      dbo.Department ON dbo.Department.DepartCode = dbo.Employee.DepartCode INNER JOIN
                      dbo.Department AS Department1 ON Department1.DepartCode = dbo.Sale.DepartCode AND
                      dbo.Department.GrpCode <> Department1.GrpCode INNER JOIN
                      dbo.Commodity ON dbo.ComFlow.ComCode = dbo.Commodity.ComCode
WHERE     (NOT (dbo.ComFlow.SalType = N'流向退貨')) OR
                      (NOT (dbo.Customer.Type = N'醫藥公司'))
雖然說，我們使用這個語句的應用是一個BI應用，實時性要求不高，但是，我覺得沒有道理會運行這么久，應該有辦法優化。

第一步，我看了看索引，好像沒有問題，都有
第二步，檢查關系，有沒有錯，沒有錯，和應用要求是一致的，尤其計算出來的結果和同事使用另外一種方法的計算結果是一致的（同事使用多個視圖分步累加）。
第三步，看看這個語句有沒有什么特別之處？
我注意到特別之處就是使用Pink底色標出的部分：
dbo.ComFlow.SalType IN (N'促銷', N'流向退貨', N'多級流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode
Or ComFlow_1.SalType IN (N'自然流向', N'自然流向退貨') AND ComFlow_1.OutCustCode = Customer_1.CustCode

這是一個Or關系的關聯？就是這個問題？
分析這個語句可以看出，這個Or語句其實是可以分解成Union語句的，所以把它變成下面的：

SELECT     dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode,
                      dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity * dbo.Commodity.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy,
                      dbo.ComFlow.FlowDate) + '-' + DATENAME(mm, dbo.ComFlow.FlowDate) + '-' + DATENAME(dd, dbo.ComFlow.FlowDate)) AS FlowDate,
                      dbo.ComFlow.SalType, dbo.Employee.DepartCode AS DepartIn, dbo.Sale.DepartCode AS DepartOut,
                      dbo.ComFlow.Quantity * dbo.Commodity.TradePrice * dbo.Commodity.Discount / 100 AS Total, dbo.Department.GrpCode AS GrpCodeIn,
                      Department1.GrpCode AS GrpCodeOut
FROM         dbo.ComFlow INNER JOIN
                      dbo.Customer ON dbo.ComFlow.SalType IN (N'促銷', N'流向退貨', N'多級流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode INNER JOIN
                      dbo.CustomerRelation ON dbo.ComFlow.ComCode = dbo.CustomerRelation.ComCode AND
                      dbo.CustomerRelation.CustCode = dbo.Customer.CustCode INNER JOIN
                      dbo.Employee ON dbo.CustomerRelation.EmpCode = dbo.Employee.EmpCode INNER JOIN
                      dbo.Sale ON dbo.ComFlow.SaleCode = dbo.Sale.SaleCode INNER JOIN
                      dbo.Department ON dbo.Department.DepartCode = dbo.Employee.DepartCode INNER JOIN
                      dbo.Department AS Department1 ON Department1.DepartCode = dbo.Sale.DepartCode AND
                      dbo.Department.GrpCode <> Department1.GrpCode INNER JOIN
                      dbo.Commodity ON dbo.ComFlow.ComCode = dbo.Commodity.ComCode
WHERE     (NOT (dbo.ComFlow.SalType = N'流向退貨')) OR
                      (NOT (dbo.Customer.Type = N'醫藥公司'))
UNION ALL
SELECT     ComFlow_1.ComFlowCode, ComFlow_1.ComCode, ComFlow_1.CustCode, ComFlow_1.DepartCode, ComFlow_1.SaleCode, ComFlow_1.EmpCode,
                      ComFlow_1.Quantity * Commodity_1.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy, ComFlow_1.FlowDate) + '-' + DATENAME(mm,
                      ComFlow_1.FlowDate) + '-' + DATENAME(dd, ComFlow_1.FlowDate)) AS FlowDate, ComFlow_1.SalType, Employee_1.DepartCode AS DepartIn,
                      Sale_1.DepartCode AS DepartOut, ComFlow_1.Quantity * Commodity_1.TradePrice * Commodity_1.Discount / 100 AS Total,
                      Department_1.GrpCode AS GrpCodeIn, Department1.GrpCode AS GrpCodeOut
FROM         dbo.ComFlow AS ComFlow_1 INNER JOIN
                      dbo.Customer AS Customer_1 ON ComFlow_1.SalType IN (N'自然流向', N'自然流向退貨') AND
                      ComFlow_1.OutCustCode = Customer_1.CustCode INNER JOIN
                      dbo.CustomerRelation AS CustomerRelation_1 ON ComFlow_1.ComCode = CustomerRelation_1.ComCode AND
                      CustomerRelation_1.CustCode = Customer_1.CustCode INNER JOIN
                      dbo.Employee AS Employee_1 ON CustomerRelation_1.EmpCode = Employee_1.EmpCode INNER JOIN
                      dbo.Sale AS Sale_1 ON ComFlow_1.SaleCode = Sale_1.SaleCode INNER JOIN
                      dbo.Department AS Department_1 ON Department_1.DepartCode = Employee_1.DepartCode INNER JOIN
                      dbo.Department AS Department1 ON Department1.DepartCode = Sale_1.DepartCode AND Department_1.GrpCode <> Department1.GrpCode INNER JOIN
                      dbo.Commodity AS Commodity_1 ON ComFlow_1.ComCode = Commodity_1.ComCode
WHERE     (NOT (ComFlow_1.SalType = N'流向退貨')) OR
                      (NOT (Customer_1.Type = N'醫藥公司'))
沒有想到，效果太明顯了，之前需要30分鐘才能運行完畢的語句只要30幾秒就完成了。