I have a query that I need help with speeding up. Right now it takes long enough that it often times out. I do know that the part of the query that times out is part of an OPENQUERY
to a linked server. The OPENQUERY
part is this:
select a._resWorkOrder
,case when max(case when a.[SSD] is null then 1 else 0 end) = 0 then MAX(a.[SSD]) end [SSD]
from (select count(wmn._resWorkOrder) HowMany
,case when wmn._resStoreroom = 'Stock' then 'Stock Material' else 'PO Material' end [Material]
,wmn._resWorkOrder
,case when wmn.cd_PercentReceived != 100 or wmn.cd_PercentReceived is null
then case when (case when count(wmn.cd_POLineItemScheduledShipDate) = count(*) then MAX(wmn.cd_POLineItemScheduledShipDate) end) is null
then null else cast((case when count(wmn.cd_POLineItemScheduledShipDate) = count(*) then MAX(wmn.cd_POLineItemScheduledShipDate) end) AS date) end
else (case when MAX(wmn.cd_POLineItemScheduledShipDate) is null then '1/1/1901' else MAX(wmn.cd_POLineItemScheduledShipDate) end)
end [SSD]
from WorkMaterial_NonFiltered wmn
join PurchaseOrderLine_NonFiltered pol on pol._resWorkOrder = wmn._resWorkOrder
and wmn._rescd_PurchaseOrderLine = pol._resPurchaseOrder + '-' + CONVERT(nvarchar,pol.Sequence)
and (pol._rescd_InfiniumStatus not in('Deleted','Canceled') or pol._rescd_InfiniumStatus is null)
where wmn.Description not like 'Contractor Labor'
group by wmn._resWorkOrder,wmn._resStoreroom,wmn.cd_PercentReceived) a
where a.Material = 'PO Material'
group by a._resWorkOrder
For testing, I usually send it into #Temp
so that I can query just this part. It does take some time. How can I speed this up?
UPDATE
What this query does: it is joining two tables the Work Material and the Purchase Order Line tables. They both have a Work Order column and the Work Material has a Purchase Order Line column that can be connected to the Purchase Order Lines Purchase Order and Sequence columns.
What is happening is I am checking the Work Material Percent Received if it is not 100% (or if it is null) then I count how many lines there are in the Work Material and compare that to how many Purchase Order Lines have a Scheduled ship Date. If those numbers match then I take the MAX
Scheduled Ship Date if they don't match then I use NULL
. If the Percent Received is 100% and all the Purchase Order Lines have not Scheduled Ship Date, then I use 1/1/1901, otherwise, I use the MAX
Scheduled Ship Date.
Then because there can be multiple dates with the same Work Order I have an outer query that groups based on the Work Order and then get the MAX
Date provided in the inner query.
This whole thing is filtered to remove any lines from the Purchase Order Lines are Deleted, Canceled or have no status from Infinium. And also remove all lines from the Work Material that are Contractor Labor.
The part that seems to be slowing things down is when I added the JOIN
condition: and wmn._rescd_PurchaseOrderLine = pol._resPurchaseOrder + '-' + CONVERT(nvarchar,pol.Sequence)
. This fixed an error that I was getting and JOIN
ed the 2 tables correctly, but really slowed it down.
[SSD OSP]
and[SSD COE]
are the same. \$\endgroup\$