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.

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