开发学院数据库 MSSQL Server SQL优化实例：从运行30分钟到运行只要30秒阅读

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

　2007-11-11 12:45:37　来源：WEB开发网　　　

核心提示：以下的SQL语句在服务器需要运行长达30分钟才能完成:SELECT dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode, dbo.ComFlo

以下的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几秒就完成了。