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

学习，学习，学习 BI/biztalk/infopath/sharepoint,呵呵。 学习没有止境。。。 博客最新内容迁移至： http://www.cleocn.com

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

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

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

评论

#1楼 2006-11-01 18:44 天轰穿      

不错.   回复  引用  查看    

#2楼 2006-11-01 18:55 .progame[未注册用户]

第一个查询中 ComFlow_1在哪里？   回复  引用    

#3楼 2006-11-01 20:09 老蒋      

楼上的兄弟看的真仔细   回复  引用  查看    

#4楼 2006-11-01 20:24 美娃~西西[未注册用户]

大哥，你的表明为什么不用缩写呢，看着好费劲呀，好长呀。

不过效果上竟然差距这么大


不错的在线个人知识管理网站
*** 美娃~西西
大家看看呀

  回复  引用    

#5楼 2006-11-01 20:37 非我      

修改后还是有OR啊，慢的原因应该不完全是它   回复  引用  查看    

#6楼[楼主] 2006-11-01 22:55 无为而为      

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

应该是

dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode 
                Or ComFlow.SalType IN (N'自然流向', N'自然流向退货') AND ComFlow.OutCustCode = Customer.CustCode
 
比较粗心，第一个语句是由第二个语句还原回去的，忘了修改了。
 
没有使用缩写是因为使用SQL Server的视图编辑器编辑的。
  回复  引用  查看    

#7楼 2006-11-02 07:42 兰亭      

嘻嘻，提示：使用SQL Server的视图编辑器也是可以使用表名简写的   回复  引用  查看    

#8楼 2006-11-02 11:16 大力      

博主的SQL是手工写的还是自动生成的，如果手动写那么长的语句请痛苦呀。   回复  引用  查看    

#9楼 2006-11-02 14:38 Henry.Liu      

楼主这个超级大SQL语句应是放在视图里面来用的,但是看到
ON dbo.ComFlow.SalType IN (N'促销', N'流向退货', N'多级流向')
有这样的实时真实值存在的SQL语句,放在视图里显然不合适,不过我想执行慢的原因并非楼主所说的那个,而是这些表结构设计存在严重问题导致.我想如果你的表结构清淅,这样的语句最多3秒钟就OK了(当然也取决于你的数据量).   回复  引用  查看    

#10楼 2006-11-02 15:39 Nina      

應該把表明縮寫，不管是view還是其它都可以的阿   回复  引用  查看    

#11楼 2006-11-04 13:29 RicCC      

修改前后逻辑上不等价，修改前是A and B or C and D，修改后是(A and B) or (C and D)
如果不是你的笔误，修改之前Join出来的结果应该比修改之后多，或者至少是dbo.ComFlow INNER JOIN dbo.Customer这个中间操作结果要多很多，所以这应该是前者比后者慢的原因

理论上讲你的修改应该并没有起到优化的作用   回复  引用  查看    

#12楼 2006-11-05 00:49 布尔      

我觉得把两个sql的查询计划图贴出来一比较就可以看到原因了。   回复  引用  查看    

#13楼[楼主] 2006-11-06 11:09 无为而为      

@RicCC
AND优先级高于Or，不信你可以到SQL Server的视图编辑器试试看，
你加了括号它还会自动去掉的括号。   回复  引用  查看    

#14楼 2006-12-15 22:58 yunhuasheng      

讲得好!!   回复  引用  查看    

#15楼 2006-12-19 15:56 随风而去[匿名]

对于内联把where后的条件提至最早允话出现的位置将显著提升速度,因常量把相当部分数据限制住了   回复  引用    

#16楼 2009-04-22 15:56 GeorgeHuang      

没想到一个地方的不懂就相差这么大，学习了。   回复  引用  查看    

#17楼 2009-06-16 10:23 游客1[未注册用户]

因为使用OR的时候是不能使用索引扫描的，导致性能下降很大，所以尽量不要使用OR   回复  引用    




发表评论

昵称： [登录] [注册]

主页：

邮箱：(仅博主可见)

评论内容：

  登录  注册

[使用Ctrl+Enter键快速提交评论]

0 547079




相关文章:

相关链接: