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

最新相关文章

• ·SQL Server 管理对象
• ·Sql server 2005 找出子表树
• ·更新数据库对象依赖关系
• ·SQL Server 2005 SysAdmin登录审计
• ·SQL Server数据库查询累计值的方法
• ·自动生成清空数据库的SQL语句
• ·选择是否恢复整个SQL Server的方法介绍
• ·讲解SQL Server数据库被挂马的解决方案
• ·SQL Server 2000的视图中必须小心使用*符号
• ·SQL Server取得网站路径的几种方法及比较
• ·SQL Server数据库对上亿表的操作
• ·教你快速确定SQL Server栏中的最大值
• ·SQLServer数据库管理常用的SQL和T-SQL语句
• ·Oracle、SQL Server中如何锁定DB和解锁DB