CODEWITHSUNDEEP
sqlsql-server
thothal
thothal

Reputation: 20379

Short circuit CASE/iif

I created a table valued function, which takes an optional parameter, which should determine whether a rather lengthy calculation (PERCENTILE_CONT in my case) should be run.

For this purpose I use a construct like this in my select statement

SELECT CASE @my_param 
       WHEN 1 THEN NULL 
       ELSE --run lengthy calc
       END

However, looking at the execution plan it seems that the lengthy calculation is triggered irrespective of how I set the parameter.

The problem can be reproduced with the following lines:

CREATE TABLE #temp(a int)
INSERT INTO #temp Values(1)
DECLARE @i INT = 1;
SELECT CASE WHEN @i=1 THEN 1
       ELSE PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY a) OVER ()
       END
  FROM #temp

If you look at the execution plan, you will see that the PERCENTILE_CONT function is included irrespective of the value of @i. Since this very function slows down my query by a bit, I was wondering how to best circumvent that?

I tried to add an IF construct paired with an UPDATE statement in my function and while this avoids running the PERCENTILE_CONT function when not needed, it slows down the function when I want to include the PERCENTILE_CONT function. So what other options do I have to run PERCENTILE_CONT only in needed cases?

My median will eventually be calculated for several subgroups, thus I decided to go for PERCENTILE_CONT in the first place, knowing that it is not the best performance wise, but is very readable. Hence, while I appreciate hints of how to calculate the median in a more efficient way, I would also be curious to learn how to avoid or short circuit the calculation without the need to fall back to an ordinary if clause.

Upvotes: 0

Views: 208

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

When SQL Server creates an execution plan for a query that uses an optional parameter, it has to generate a plan that is capable of handling both scenarios:

  • where a value is provided for a specific parameter
  • where no value is provided for that parameter

When a parameter is provided, it takes into account cardinality estimates based on that initial value (or lack of value), and then keeps that plan for subsequent executions (which is why performance can differ when the parameter values change but the plan remains the same).

This whole process gets exponentially more complex as you go from 1 to n optional parameters.

If you want to have one plan for the presence of a specific parameter, and another one for the absence, or even different plans compiled for different values of the param, you can use conditional logic like Gordon suggested or you can follow the kitchen sink approach.

For example:

SET @sql = N'SELECT ...';

SET @sql = @sql + CASE WHEN @my_param = 1 THEN N'1'
  ELSE N'PERCENTILE_CONT(0.5) ... ' END;

...

EXEC sys.sp_executesql @sql;

I think this is a good balance between "one plan fits all" and "use OPTION (RECOMPILE) to get a new plan every single time." You can even add OPTION (RECOMPILE) to the dynamic SQL statement conditionally (for example you don't need it when the search ends up being a seek on a unique key, but it can make sense to have it when you might be searching for last name starts with S vs. last name contains Q).

Upvotes: 3

sacse
sacse

Reputation: 3744

You can use Option (Recompile) to get the desired execution plan.

Please see the result in the screenshot below:

enter image description here

Upvotes: 1

Related Questions