当前位置：首页>>网络编程>>数据库>>正文

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

文章出处：设计前沿收集 作者：未知　发布时间：2006-11-04 收藏到QQ书签

以下的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'医药公司'))

Google