Post reply

Is it worth splitting a stored procedure for performance reasons?

  • April 3, 2015 at 10:08 am

    #398277

    I have a stored procedure that takes as input many parameters from the user interface related to the users' filtering options to be displayed, but there is one specific parameters that have significant impact on performance depending on the value, and that's because the filter is not about a value or range of values, but different logic altogether.

    Currently the logic is embedded in one stored procedure. The problem is that is different users have different options for the parameter, the query plan may not be optimal for some of the users. I wonder if I split the procedure into different procedures depending on the user selected option. This way, the different procedures would have their own saved query plan. The original procedure would be modified to call the appropriate sub-procedure depending on the option.

  • April 3, 2015 at 11:02 am

    #1788620

    I wonder if I split the procedure into different procedures depending on the user selected option. This way, the different procedures would have their own saved query plan.

    This is something that is certainly worth testing. The problem is you now have more stored procs to maintain and manage when you add/change logic.

    Another option might be to run the stored proc with OPTION (RECOMPILE). If the compile time is not bad this will make it much more likely that each plan produced is optimal for the user-selected parameters.

    You might also want to take a look at this article: SQL in the Wild: Catch-all queries[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • April 3, 2015 at 11:14 am

    #1788625

    Sounds like a great place to put a wrapper proc. Have it have the same name and parameters as your current procedure. Then, inside it, it figures out which of two other procedures to call based on the parameters being passed in. That way, each procedure can have its own execution plan, they'll recompile independently, and, best of all, you don't have to rewrite the front end to make this happen.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 3, 2015 at 11:31 am

    #1788631

    Are you sure the problem is the compiled execution plan and not just a problem with the code down the slower branch?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply