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

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

 2007-05-19 09:42:13 来源:WEB开发网   
核心提示:以下的SQL语句在服务器需要运行长达30分钟才能完成:SELECT dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode,dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode,dbo.ComFlow.Em

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

1 2  下一页

Tags:SQL SERVER 优化

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接