sql - 如何防止在我的查询中发生延迟假脱机?
问题描述
我一直在努力优化这个查询,
SELECT
dbo.OE61BLIN.Order_Key
,dbo.OE61BLIN.Doc_Type
,dbo.OE61BHED.Doc__
,dbo.OE61BHED.Inv_Date
,dbo.OE61BHED.Cust__
,dbo.OE61BLIN.Line_Type
,dbo.OE61BLIN.Item__
,dbo.OE61BLIN.Description
,(CASE
WHEN dbo.OE61BLIN.Doc_Type = 'I' THEN dbo.OE61BLIN.Qty_Shipped * dbo.OE61BLIN.Unit_Factor
WHEN dbo.OE61BLIN.Doc_Type = 'C' AND
dbo.OE61BLIN.return_to_inventory_ = 1 THEN -dbo.OE61BLIN.Qty_Shipped * dbo.OE61BLIN.Unit_Factor
ELSE 0
END) AS QTY
,(CASE
WHEN dbo.OE61BLIN.Doc_Type = 'I' THEN dbo.OE61BLIN.Ext_Price
WHEN dbo.OE61BLIN.Doc_Type = 'C' THEN -dbo.OE61BLIN.Ext_Price
ELSE 0
END) * (CASE
WHEN ISNULL(dbo.OE61BHED.Inv_Disc__, 0) <> 0 THEN 1 - (dbo.OE61BHED.Inv_Disc__ / 100)
ELSE 1
END)
AS amount
,dbo.OE61BHED.Inv_Disc__
,dbo.OE61BLIN.ITEM_GROUP
,dbo.OE61BLIN.Category
,ISNULL(dbo.AR61ACST.intercompany, 0) AS intercompany
FROM dbo.OE61BHED
LEFT OUTER JOIN dbo.AR61ACST
ON dbo.OE61BHED.Cust__ = dbo.AR61ACST.Cust__
RIGHT OUTER JOIN dbo.OE61BLIN
ON dbo.OE61BHED.Order_Key = dbo.OE61BLIN.Order_Key
WHERE (dbo.OE61BLIN.Line_Type = 'R')
AND isnull(intercompany,0) != 1
AND (dbo.OE61BLIN.Doc_Type = 'C'
OR dbo.OE61BLIN.Doc_Type = 'I')
完整的估计执行计划在这里 https://www.brentozar.com/pastetheplan/?id=S1htt0rxN
实际执行计划 https://www.brentozar.com/pastetheplan/?id=BymztxLgE
我使用SQL Sentry Plan Explorer对其进行优化,它建议我应该添加以下两个索引,我有但它并没有太大改善,它只是从计划中删除了RID Look Up。
CREATE NONCLUSTERED INDEX [XI_LineTypeDocType_OE61BLIN_12172018]
ON [dbo].[OE61BLIN] ([Line_Type],[Doc_Type])
INCLUDE ([Order_Key],[Item__],[Description],[Category],[Return_to_Inventory_],[Unit_Factor],[Qty_Shipped],[Ext_Price],[ITEM_GROUP])
CREATE INDEX [XI_CustIntercompany_AR67ACST_12172018] ON [GarbageMark].[dbo].[AR61ACST]
([Cust__] ASC)
INCLUDE ([Intercompany])
我完全坚持如何解决这个问题。
我看到 Lazy Spool 是最昂贵的操作,但我不知道如何删除或替换。
解决方案
遗憾的是,您没有intercompany
在 where 子句中使用其表名作为前缀,因此在某种程度上我猜测您在下面看到的更改。我将建议您重新安排查询以避免使用右外连接,然后,也许更重要的是,将intercompany <> 1
条件直接放入左连接中,从而ISNULL()
从 where 子句中删除使用。
SELECT
dbo.OE61BLIN.Order_Key
, dbo.OE61BLIN.Doc_Type
, dbo.OE61BHED.Doc__
, dbo.OE61BHED.Inv_Date
, dbo.OE61BHED.Cust__
, dbo.OE61BLIN.Line_Type
, dbo.OE61BLIN.Item__
, dbo.OE61BLIN.Description
, (CASE
WHEN dbo.OE61BLIN.Doc_Type = 'I' THEN dbo.OE61BLIN.Qty_Shipped * dbo.OE61BLIN.Unit_Factor
WHEN dbo.OE61BLIN.Doc_Type = 'C' AND
dbo.OE61BLIN.return_to_inventory_ = 1 THEN -dbo.OE61BLIN.Qty_Shipped * dbo.OE61BLIN.Unit_Factor
ELSE 0
END) AS QTY
, (CASE
WHEN dbo.OE61BLIN.Doc_Type = 'I' THEN dbo.OE61BLIN.Ext_Price
WHEN dbo.OE61BLIN.Doc_Type = 'C' THEN -dbo.OE61BLIN.Ext_Price
ELSE 0
END) * (CASE
WHEN ISNULL( dbo.OE61BHED.Inv_Disc__, 0 ) <> 0 THEN 1 - (dbo.OE61BHED.Inv_Disc__ / 100)
ELSE 1
END)
AS amount
, dbo.OE61BHED.Inv_Disc__
, dbo.OE61BLIN.ITEM_GROUP
, dbo.OE61BLIN.Category
, ISNULL( dbo.AR61ACST.intercompany, 0 ) AS intercompany
FROM dbo.OE61BLIN
INNER JOIN dbo.OE61BHED ON dbo.OE61BLIN.Order_Key = dbo.OE61BHED.Order_Key
LEFT OUTER JOIN dbo.AR61ACST ON dbo.OE61BHED.Cust__ = dbo.AR61ACST.Cust__
AND dbo.AR61ACST.intercompany != 1
WHERE dbo.OE61BLIN.Line_Type = 'R'
AND dbo.OE61BLIN.Doc_Type IN ('C','I')
;
我相信 OE61BLIN 和 OE61BHED 之间的连接可以是内连接,如果不尝试使用左连接。
推荐阅读
- python - Python/BigQuery 查询作业到字典
- azure - 测试在 Postman 集合中有效,但在 Newman 中显示为 0 执行
- javascript - 未捕获的类型错误:无法读取 null 的属性“getElementsByClassName”| HTML |JS
- python - 将空数值从 Python 插入到雪花中
- python - 如何在另一个正在运行时打开子进程?
- r - 如何从数据集中选择某些行?
- javascript - 如何在角度 8 的表 td 中显示多选下拉值
- python - 从对等包中导入 conftest.py
- python - AttributeError:“dict”对象没有属性“parseArrivalDate”
- python - 生成 3D 坐标的最佳拟合曲线