SQL SERVER 优化实例:从运行30分钟到运行只要30秒
2007-05-19 09:42:13 来源:WEB开发网核心提示: 虽然说,我们使用这个语句的应用是一个BI应用,SQL SERVER 优化实例:从运行30分钟到运行只要30秒(2),实时性要求不高,但是,但是效率低,使用Union虽然冗长,我觉得没有道理会运行这么久,应该有办法优化
虽然说,我们使用这个语句的应用是一个BI应用,实时性要求不高,但是,我觉得没有道理会运行这么久,应该有办法优化。
第一步,我看了看索引,好像没有问题,都有
第二步,检查关系,有没有错,没有错,和应用要求是一致的,尤其计算出来的结果和同事使用另外一种方法的计算结果是一致的(同事使用多个视图分步累加)。
第三步,看看这个语句有没有什么特别之处?
我注意到特别之处就是使用底色标出的部分:
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
这是一个Or关系的关联?就是这个问题?
分析这个语句可以看出,这个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虽然冗长,但是用在这里效率要高。
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
- ››SQL SERVER无法安装成功,sqlstp.log文件提示[未发...
- ››Sql Server中通过父记录查找出所有关联的子记录
- ››SqlServer触发器、存储过程和函数
- ››SQL Server 中的事务(含义,属性,管理)
- ››Sqlite数据库插入和读取图片数据
- ››Sql server 2005拒绝了对对象 'xx表' (数...
- ››Sql server 2005拒绝了对对象 'xx表' (数...
更多精彩
赞助商链接