以下的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应用,实时性要求不高,但是,我觉得没有道理会运行这么久,应该有办法优化。
第一步,我看了看索引,好像没有问题,都有
第二步,检查关系,有没有错,没有错,和应用要求是一致的,尤其计算出来的结果和同事使用另外一种方法的计算结果是一致的(同事使用多个视图分步累加)。
第三步,看看这个语句有没有什么特别之处?
我注意到特别之处就是使用底色标出的部分:
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关系的关联?就是这个问题?
零七网部分新闻及文章转载自互联网,供读者交流和学习,若有涉及作者版权等问题请及时与我们联系,以便更正、删除或按规定办理。感谢所有提供资讯的网站,欢迎各类媒体与零七网进行文章共享合作。