SQL Query Performance with case statement

Question

I have a simple select that is running very slow and have narrowed it down to one particular where statement.

I am not sure if you need to see the whole query, or maybe will be able to help me understand why the case is affecting performance so much. I feel like I found the problem, but can't seem to resolve it. I've worked with case statement before, and have never ran into such huge performance issues.

For this particular example. the declaration is as follows: Declare @lastInvOnly as int = 0

the problem where statement follow and runs for about 20 seconds:

AND ird.inventorydate = CASE WHEN @lastinvonly=0 THEN  
-- get the last reported inventory in respect to the specified parameter
    (SELECT MAX(ird2.inventorydate)
     FROM   irdate ird2
     WHERE  ird2.ris =r.ris AND 
            ird2.generateddata!='g' AND 
            ird2.inventorydate <= @inventorydate)
END

Removing the case makes it run in 1 second which is a HUGE difference. I can't understand why.

AND ird.inventorydate = 
    (SELECT MAX(ird2.inventorydate) 
     FROM   irdate ird2
     WHERE  ird2.ris = r.ris AND 
            ird2.generateddata! = 'g' AND 
            ird2.inventorydate <= @inventorydate)

The execution plan is essential to troubleshoot the performance issue. Could you add it, please? — Gen Wan
– Gen Wan, Commented Apr 22, 2019 at 19:43
You're probably having a problem with a bad plan caused by the CASE expression. What happens if @lastinvonly =1? — Luis Cazares
– Luis Cazares, Commented Apr 22, 2019 at 19:43
but @lastinvonly is declared, and i hard code it as 0. why does it effect execution plan? thanks for answers! — Madam Zu Zu
– Madam Zu Zu, Commented Apr 22, 2019 at 19:45
@MadamZuZu . . . First, case in the where clause impedes the optimizer and can kill performance. Second, there are probably better ways to express this logic using window functions, but you don't provide enough context to make a suggestion. — Gordon Linoff
– Gordon Linoff, Commented Apr 22, 2019 at 19:55

UnhandledExcepSean · Accepted Answer · 2019-04-22 19:50:07Z

1

It should almost certainly be a derived table and you should join to it instead. Sub-selects tend to have poor performance and when used conditionally, even worse. Try this instead:

INNER JOIN (
   select
       ris
      ,max(inventorydate) AS [MaxInvDate]
   from irdate
   where s and generateddata!='g'
   and inventorydate <= @inventorydate
   GROUP BY ris
) AS MaxInvDate ON MaxInvDate.ris=r.ris
and ird.inventorydate=MaxInvDate.MaxInvDate
and @lastinvonly=0

I'm not 100% positive this logically works with the whole query as your question only provides a small part.

answered Apr 22, 2019 at 19:50

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Madam Zu Zu Over a year ago

i think it worked... i will need to test the numbers and see how to apply this approach to the other case statements i have commented out during testing. i will mark this as correct tomorrow, when i have time to go through everything and, more importantly, understand how and why it works :) thank you again!

Kittoes0124 · Accepted Answer · 2019-04-22 22:30:02Z

I can't tell for sure without seeing an execution plan but the branch in your filter is likely the cause of the performance problems. Theoretically, the optimizer can take the version without the case and apply an optimization that transforms the subquery in your filter into a join; when the case statement is added this optimization is no longer possible and the subquery is executed for every row. One can refactor the code to help the optimizer out, something like this should work:

outer apply (
    select max(ird2.inventorydate) as maxinventorydate
    from irdate ird2
    where ird2.ris = r.ris
      and ird2.generateddata <> 'g'
      and ird2.inventorydate <= @inventorydate
      and @lastinvonly = 0
) as ird2
where ird.inventorydate = ird2.maxinventorydate

Collectives™ on Stack Overflow

SQL Query Performance with case statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related