Speed up SQL query

Question 1

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 anOPENQUERY to a linked server. TheOPENQUERY 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 itinto #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 theMAX Scheduled Ship Date if they don't match then I useNULL. 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 theMAX 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 theMAX 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 theJOIN condition:and wmn._rescd_PurchaseOrderLine = pol._resPurchaseOrder + '-' + CONVERT(nvarchar,pol.Sequence). This fixed an error that I was getting andJOINed the 2 tables correctly, but really slowed it down.

Question 2

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.

Question 3

@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.

Question 4

@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.

Question 5

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 .

Question 6

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.

Question 7

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.
You can also simplify yourcase expressions usingcoalesce() to augment how you are handlingnull values and their comparisons. This may or may not impact performance, but I believe it makes the code easier to understand.
You may or may not see a difference by moving the outerwhere 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]);goselect    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  ) agroup by a._resWorkOrder

Other notes and reference:

SARGable expressions and performance - Daniel Hutmachier
Properly Persisted Computed Columns - Paul White
Bad habits to kick : declaringvarchar without (length) - Aaron Bertrand - you should always provide a length for allvarchar ornvarchar variables/parameters.
The only truly safe formats for date/time literals in SQL Server, at least fordatetime andsmalldatetime, are:YYYYMMDD andYYYY-MM-DDThh:mm:ss[.nnn] -Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
Aliases should be more explicit, use1 as x orx = 1; not1 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

Question 8

@Mike Happy to help!

SqlZim 1363 bronze badges · Accepted Answer · 2017-10-23 20:39:24Z

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.
You can also simplify yourcase expressions usingcoalesce() to augment how you are handlingnull values and their comparisons. This may or may not impact performance, but I believe it makes the code easier to understand.
You may or may not see a difference by moving the outerwhere 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]);goselect    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  ) agroup by a._resWorkOrder

Other notes and reference:

SARGable expressions and performance - Daniel Hutmachier
Properly Persisted Computed Columns - Paul White
Bad habits to kick : declaringvarchar without (length) - Aaron Bertrand - you should always provide a length for allvarchar ornvarchar variables/parameters.
The only truly safe formats for date/time literals in SQL Server, at least fordatetime andsmalldatetime, are:YYYYMMDD andYYYY-MM-DDThh:mm:ss[.nnn] -Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
Aliases should be more explicit, use1 as x orx = 1; not1 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

Movatterモバイル変換

Stack Exchange Network

Speed up SQL query

1 Answer1

You mustlog in to answer this question.

Related

Hot Network Questions

Subscribe to RSS