Share via

Use Condition-Specific Stored Procedures

  • Article

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