Use Condition-Specific Stored Procedures


The optimizer chooses the best plan for an SP given the current parameter values.  This plan is then reused regardless of whether the nature of the parameters changes from call to call.  If an application is aware that input parameters have a small number of significant behaviors, then the application may use condition-specific SPs. 


For example, if an SP has a single parameter which can be either NULL or non-NULL, and a NULL value selects very few rows while a non-NULL value selects many more, then this technique may be used.  The application would code the SP twice.  The two SPs will have different names but the SP contents will be the same.  The logic that calls the SP must call the appropriate SP depending on whether the parameter value is NULL or non-NULL.  SPs are compiled the first time they are called.  As a result, the SP for NULL will be optimized for a NULL value and will be subsequently only called when the parameter value is NULL.  The SP for a non-NULL parameter will similarly be optimized for a non-NULL parameter value and only be called with a non-NULL parameter value.  This method can result in dramatic improvements in performance when the degree of selectivity of parameters varies significantly.


Comments (7)

  1. Maurits says:

    I frequently find myself needing things like

    CREATE PROCEDURE WidgetSearch

    (

    __ @Foo varchar(50) = ”,

    __ @Bar int = 0,

    __ @Baz bit = NULL,

    __ …

    )

    AS

    SELECT

    __ *

    FROM

    __ Widgets

    WHERE

    __ ((@Foo = ”) OR Foo = @Foo) AND

    __ ((@Bar = 0) OR Bar = @Bar) AND

    __ ((@Baz IS NULL) OR (Baz = @Baz)) AND

    __ …

    RETURN (0)

    However I’ve noticed this is rather slow, and the following kind of thing returns the same results much faster…

    I don’t like this second kind very much, because it lends itself much more to coder error…

    CREATE PROCEDURE WidgetSearch

    (

    __ @Foo varchar(50) = ”,

    __ @Bar int = 0,

    __ @Baz bit = NULL,

    __ …

    )

    AS

    DECLARE @SQL varchar(8000)

    SELECT @SQL =

    ‘SELECT

    __ *

    FROM

    __ Widgets

    WHERE

    __ ‘ +

    CASE

    __ WHEN @Foo = ” THEN ”

    __ ELSE ‘Foo = ”’ + REPLACE(@Foo, ””, ”””) + ”’ AND ‘

    END +

    CASE

    __ WHEN @Bar = 0 THEN ”

    __ ELSE ‘Bar = ‘ + CAST(@Bar AS VARCHAR(20)) + ‘ AND ‘

    CASE

    __ WHEN @Baz IS NULL THEN ”

    __ ELSE ‘Baz = ‘ + CAST(@Baz AS VARCHAR(20)) + ‘ AND ‘ +

    END +

    __ …

    __ 1 = 1

    EXECUTE sp_executesql @SQL

    RETURN (0)

    Is there a better way?

  2. Amin says:

    Thanks Ian!

    What about EXEC WITH RECOMPILE? If the SP is simple but the query’e result very dependent on parameters I think RECOMILE is fine?

    Maurits! Have look at this:

    http://www.sommarskog.se/dyn-search.html#dynsummary

  3. ianjo says:

    The most common case of this problem is described by Maurits, a statement may or may not restricted with a given parameter.  If the statement simply has the pattern (@p = foo || @p =” ) then the query will be optimized for the initial parameter values.  It should be fast for those values, but may be slow for other sets of parameter values.  The recourse mentioned by Maurits of generating a SQL statement on the fly (what we call ad hoc SQL) should produce a fast plan for all cases, but has the disadvantage of requiring the statement to be compiled for each and every invocation of the SP.  Amin is correct that a cleaner way of writing the ad hoc SQL is with the WITH RECOMPILE option which can either be issued on the call to the SP, or in the creation of the SP itself.  The article referred to by Jerry does not conflict wih this post.  However, this is only because they specifically call out cases in which the same plan is produced for all sets of parameters.

    The case here is one in which different parameter sets need different plans, and where we do not with to recompile each and every time the statement is inoked.  This is what makes this problem truly hard.  At the same time, it is an important problem to solve.  Compilation is extradorinairlely expensive, often measured in seconds for complex statements, and bad plans can have even more dramatic effects.  If run-time of a good plan is long enough that compilation is not significant, then compiling each and every time may be acceptable.  If the worst plan is not so much worse than the best plan, then use a single parameterized statement.  However, if you need the very best plan and cannot afford to compile each time, then consider using per case statements.

    Thanks for the comments!!! Ian.