cleo-凡事从积极的态度做起

学习,学习,学习 BI/biztalk/infopath/sharepoint,呵呵。 学习没有止境。。。

导航

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

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

这里可以看出,Or的语句可能破坏了索引的作用。使用Or进行关联虽然逻辑非常清楚,但是效率低。
使用Union虽然冗长,但是用在这里效率要高。

posted on 2006-11-01 18:22  无为而为-凡事从积极的态度做起  阅读(14373)  评论(18编辑  收藏  举报