Ian Jose's WebLog : Use Condition-Specific Stored Procedures

Microsoft.com Home

Site Map

Blogs Home

Ian Jose's WebLog

This weblog is intended to help T-SQL developers get the best performing query plans from SQL Server. Some of the posts describe simple mistakes that can be easily avoided. Other posts describe complex solutions to limitations in SQL Server. Lastly, some posts describe recent improvements in SQL Server, and the means to employ them best.

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.

Published Thursday, November 10, 2005 2:00 PM by ianjo

Comments

	Maurits said: 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? November 10, 2005 7:16 PM
	Amin said: 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 March 30, 2006 4:36 PM
	Jerry Nixon said: Your comments seem in conflict with http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx April 21, 2006 1:32 PM
	ianjo said: 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. June 6, 2006 7:50 PM
	case usage in where condition in sql said: PingBack from http://xzavier.finestlovestories.com/caseusageinwhereconditioninsql.html June 29, 2008 8:18 AM
	Ian Jose s WebLog Use Condition Specific Stored Procedures \| Paid Surveys said: PingBack from http://paidsurveyshub.info/story.php?id=75516 June 2, 2009 4:05 AM
	Ian Jose s WebLog Use Condition Specific Stored Procedures \| bar stools said: PingBack from http://barstoolsite.info/story.php?id=4467 June 19, 2009 2:58 AM