I been struggling to optimize this query,
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')
Complete estimated execution plan is here https://www.brentozar.com/pastetheplan/?id=S1htt0rxN
Actual Exectuion Plan https://www.brentozar.com/pastetheplan/?id=BymztxLgE
I use SQL Sentry Plan Explorer to optimaze it , and it suggested that I should add the following two indexes, which I have But it doesnt improve much, It only removed RID Look Up from plan.
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])
I am completely stuck on how to aproach this problem.
I see that Lazy Spool is the most expensive operation but I dont know how to remove or substitute.
Regrettably you don't prefix intercompany
in the where clause with its table name so to some extent I'm guessing that the changes you see below. I am going to suggest that you re-arrange your query to avoid the use of right outer join and then, perhaps more importantly, place the intercompany <> 1
condition directly into the left join which
removes the use of ISNULL()
from your where clause.
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')
;
I believe the join between OE61BLIN and OE61BHED can be an inner join, if not try using a left join.