3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Sep 28 '17 at 20:26
  • \$\begingroup\$ @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. \$\endgroup\$ – Mike Sep 29 '17 at 12:20
  • \$\begingroup\$ @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. \$\endgroup\$ – Mike Sep 29 '17 at 12:43
  • \$\begingroup\$ 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 . \$\endgroup\$ – MJ8 Oct 4 '17 at 20:29
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – SqlZim Oct 22 '17 at 13:43
1
\$\begingroup\$
  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:


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
\$\endgroup\$
  • \$\begingroup\$ @Mike Happy to help! \$\endgroup\$ – SqlZim Oct 24 '17 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.