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?
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
and wmn._rescd_PurchaseOrderLine = pol._resPurchaseOrder + '-' + CONVERT(nvarchar,pol.Sequence). This fixed an error that I was getting and
JOINed the 2 tables correctly, but really slowed it down.