科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道优化实例:从运行30分钟到运行只要30秒(2)

优化实例:从运行30分钟到运行只要30秒(2)

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

以下的SQL语句在服务器需要运行长达30分钟才能完成:

作者:cleo 来源:cnblogs 2007年9月10日

关键字: SQL Server 优化 数据库 SQL Server 各版本

  • 评论
  • 分享微博
  • 分享邮件

分析这个语句可以看出,这个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虽然冗长,但是用在这里效率要高。

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章