当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL优化实例：从运行30分钟到运行只要30秒

MSSQL
SQL Server SA权限总结经典技术
ASP数据库编程SQL常用技巧
SQL SERVER数据库开发之存储过程应用
SQL Server 2000的安全配置
MSSQL经典语句
SQL 经典语句
有用的SQL语句（删除重复记录，收缩日志）
Access 数据类型与 MS SQL 数据类型的相应
SQL语句示例
SQL数据类型详解
将Sql Server对象的当前拥有者更改成目标拥有者
MSSQL内外连接(INNER JOIN)语句详解
SQL 外链接操作小结 inner join left join right join
SQL Server中网络备份一例
SQL语句导入导出大全
SQL 新增/修改 表字段列的类型等
系统存储过程，sp_executesql
sql2005开启xp_cmdshell
实例学习SQL的Select命令
删除数据库中重复数据的几个方法

No. « 3 4 5 6 »

技术文章搜索

关键字

MSSQL 中的 SQL优化实例：从运行30分钟到运行只要30秒

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-30   浏览: 44 ::
收藏到网摘: n/a

 以下的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应用，实时性要求不高，但是，我觉得没有道理会运行这么久，应该有办法优