以下的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應用，實時性要求不高，但是，我覺得沒有道理會運行這麼久，應該有辦法優化。
第一步，我看了看索引，好像沒有問題，都有
第二步，檢查關系，有沒有錯，沒有錯，和應用要求是一致的，尤其計算出來的結果和同事使用另外一種方法的計算結果是一致的（同事使用多個視圖分步累加）。
第三步，看看這個語句有沒有什麼特別之處？
我注意到特別之處就是使用底色標出的部分：
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
