SQL SERVER 优化实例：从运行30分钟到运行只要30秒

SQL SERVER 优化实例：从运行30分钟到运行只要30秒2008-05-10 本站搜集整理以下的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