Speed up SQL query

Question

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 JOINed the 2 tables correctly, but really slowed it down.

• 200_success: Please tell us what this query accomplishes, and show us the relevant parts of the schema, including indexes. Including the query plan would also be helpful.
• Mike: @200_success I've added more information about what the query is doing. I'm not sure how to get and add the query plan. I've been able to pull the Execution Plan, but not sure that is what you are asking to see.
• Mike: @200_success I tried adding the images I took of the Execution Plan and kept getting an error about my question having unformatted code even though I was only adding the images.
• MJ8: use actual execution plan to find out what part of your query is the most resource intensive . Free 3rd party tool from www.sentryone.com may help .
• SqlZim: The added join condition is not SARGable - SARGable expressions and performance - Daniel Hutmachier. Also, the expressions for [SSD OSP] and [SSD COE] are the same.

Answer 1

1. Create a computed column and a covering index on the other server to solve the non SARGable issue in the join. Simple dbfiddle.uk demo of a join on computed column in a covering index.

2. You can also simplify your case expressions using coalesce() to augment how you are handling null values and their comparisons. This may or may not impact performance, but I believe it makes the code easier to understand.

3. You may or may not see a difference by moving the outer where clause to the inner query, depending on if it is already being optimized by the engine.

---

alter table PurchaseOrderLine_NonFiltered add [_rescd_PurchaseOrderLine] as 
 ([_resPurchaseOrder] + '-' + convert(varchar(10),[Sequence])) persisted;
create nonclustered index ix_PurchaseOrderLine_NonFiltered__rescd_PurchaseOrderLine 
 on PurchaseOrderLine_NonFiltered ([_resWorkOrder],[_rescd_PurchaseOrderLine])
 include ([_rescd_InfiniumStatus]);
go
select
 a._resWorkOrder
 , case when max(case when a.[SSD] is null then 1 else 0 end) = 0 then max(a.[SSD]) end as [SSD]
from (
 select
 count(wmn._resWorkOrder) as HowMany
 , wmn._resWorkOrder
 , case
 when (wmn.cd_PercentReceived != 100 or wmn.cd_PercentReceived is null)
 and coalesce(count(wmn.cd_POLineItemScheduledShipDate),-1) != count(*)
 then null
 else coalesce(max(wmn.cd_POLineItemScheduledShipDate),'19010101')
 end as [SSD]
 from WorkMaterial_NonFiltered wmn
 inner join PurchaseOrderLine_NonFiltered pol
 on pol._resWorkOrder = wmn._resWorkOrder
 and wmn._rescd_PurchaseOrderLine = pol._rescd_PurchaseOrderLine 
 and (pol._rescd_InfiniumStatus not in('Deleted','Canceled') or pol._rescd_InfiniumStatus is null)
 where wmn.Description not like 'Contractor Labor'
 and (wmn._resStoreroom <>'Stock' or wmn.resStoreroom is null)
 group by wmn._resWorkOrder,wmn._resStoreroom,wmn.cd_PercentReceived
 ) a
group by a._resWorkOrder

Other notes and reference:

• SARGable expressions and performance - Daniel Hutmachier
• Properly Persisted Computed Columns - Paul White
• Bad habits to kick : declaring varchar without (length) - Aaron Bertrand - you should always provide a length for all varchar or nvarchar variables/parameters.
• The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn] - Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
• Aliases should be more explicit, use 1 as x or x = 1; not 1 x - Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3) - Aaron Bertrand

---

Here is the same stuff, just as comments in the code

select
 a._resWorkOrder
 -- case expression to return `null` if any SSD are `null`
 , case when max(case when a.[SSD] is null then 1 else 0 end) = 0
 then MAX(a.[SSD])
 end as [SSD]
from (
 -- add ` as ` before aliases for greater clarity
 select
 count(wmn._resWorkOrder) as HowMany
 -- removed [Material] case expression after moving outer where to inner query
 --, case when wmn._resStoreroom = 'Stock' then 'Stock Material' else 'PO Material' end as [Material]
 , wmn._resWorkOrder
 , case
 when (wmn.cd_PercentReceived != 100 or wmn.cd_PercentReceived is null)
 -- alternate code to solve inequality comparison with possible `null`:
 and coalesce(count(wmn.cd_POLineItemScheduledShipDate),-1) != count(*) -- moved up from nested cases
 then null
 /* -- original code used to solve inequality comparison with possible `null`:
 (
 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)
 */
 -- alternate code to replace `null` value for ShipDate
 -- coalesce() would work instead of coalesce() as well
 -- using universal date literals YYYYMMDD instead of culture specific 
 else coalesce(max(wmn.cd_POLineItemScheduledShipDate),'19010101')
 /* -- original code to replace `null` value for ShipDate
 (
 case
 when max(wmn.cd_POLineItemScheduledShipDate) is null
 then '1/1/1901'
 else max(wmn.cd_POLineItemScheduledShipDate)
 end)
 */
 end as [SSD]
 from WorkMaterial_NonFiltered wmn
 inner join PurchaseOrderLine_NonFiltered pol
 on pol._resWorkOrder = wmn._resWorkOrder
 -- non-sargable condition 
 -- conversion to `(n)varchar() without specifying length:
 and wmn._rescd_PurchaseOrderLine = pol._resPurchaseOrder + '-' + CONVERT(varchar(10),pol.Sequence)
 and (pol._rescd_InfiniumStatus not in('Deleted','Canceled') or pol._rescd_InfiniumStatus is null)
 where wmn.Description not like 'Contractor Labor'
 -- moved outer where to inner query:
 and (wmn._resStoreroom <>'Stock' or wmn.resStoreroom is null)
 group by wmn._resWorkOrder,wmn._resStoreroom,wmn.cd_PercentReceived
 ) a
-- original outer where clause
--where a.Material = 'PO Material'
group by a._resWorkOrder

• SqlZim: @Mike Happy to help!