Performance Considerations for Conditional Queries (Search Forms)

Question

I frequently find stored procedures with code like this:

SELECT columns
FROM table_source
WHERE 
    (@Param1 IS NULL OR Column1 LIKE @Param1)
AND (@Param2 IS NULL OR Column2 = @Param2)
AND (@Param3 IS NULL OR ISNULL(Column3,'') LIKE @Param3 + '%')
…

Is this any better than something like this:

WHERE 
    (Column1 LIKE COALESCE(@Param1, Column1))
AND (Column2  = COALESCE(@Param2, Column2))
AND (ISNULL(Column3,'') LIKE COALESCE(@Param3 + '%', ISNULL(Column3, '')))
…

and does it matter if I pull out the expressions which depend only on parameters

DECLARE @Param3Search nvarchar(30);
SET @Param3Search = @Param3 + '%';

then use @Param3Search instead of @Param3?

Sorry to ask something so broad, but I'm sure there are some general rules of thumb for writing such queries. I just couldn't find an existing question on this.

Answer 1

The definitive articles on this topic are linked from Dynamic Search Conditions in T-SQL

Your question is tagged SQL Server 2008. If you are on at least SP1 CU5 then you can take advantage of the "Parameter Embedding Optimization" behaviour as an alternative to dynamic SQL.

SELECT columns
FROM   table_source
WHERE  ( @Param1 IS NULL
          OR Column1 LIKE @Param1 )
       AND ( @Param2 IS NULL
              OR Column2 = @Param2 )
       AND ( @Param3 IS NULL
              OR ISNULL(Column3, '') LIKE @Param3 + '%' )
OPTION (RECOMPILE); 

Will be recompiled on each invocation and be able to take account of the actual variable/parameter values for that execution.

Assume for the moment that all are NOT NULL. The plan will be compiled for

SELECT columns
FROM   table_source
WHERE  Column1 LIKE @Param1
       AND Column2 = @Param2
       AND ISNULL(Column3, '') LIKE @Param3 + '%' 

(I'd probably look to see if expanding out the predicate on Column3 led to better plans too)

Now assume that they are all NULL. The plan ought to simplify to

SELECT columns
FROM   table_source

This may be more maintainable than the dynamic SQL approach and means fewer possibly single use plans in the cache but does have the additional overhead of recompilation.

Answer 2

I normally use Dynamic SQL For this purpose .

Something like .....

DECLARE @Param1 [DataType]
DECLARE @Param2 [DataType]
DECLARE @Param3 [DataType]

DECLARE @SQL NVARCHAR(MAX);


SET @SQL = N'SELECT columns FROM table_source  WHERE 1 = 1 '
           + CASE WHEN @Param1 IS NOT NULL 
                THEN N' AND Column1 LIKE @Param1 '        ELSE N' ' END
           + CASE WHEN @Param2 IS NOT NULL 
                THEN N' AND Column2 = @Param2 '           ELSE N' ' END
            + CASE WHEN @Param3 IS NOT NULL 
                THEN N' AND Column3 LIKE @Param3 +''%'' ' ELSE N' ' END 

EXECUTE sp_executesql @SQL
                    ,N'@Param1 DataType, @Param2 DataType, @Param3 DataType'
                    ,@Param1
                    ,@Param2
                    ,@Param3

The problem with other approach (@Param2 IS NULL OR Column2 = @Param2) is Sql Server does not short-circuit queries like this. Even if the parameter is null it still might go ahead and tries to evaluate the expressions Column2 = @Param2 .

Therefore using dynamic sql you build your queries depending on the variables and then execute the query only with required where clauses.

Also using Dynamic sql inside your stored procedures give you the ability of having parametrised execution plans for a stored procedure.

With your current approach Parameter sniffing will suck the performance out of a very simple query.

Moral of the story: stick to dynamic sql with these kind of optional parameters and use sp_executesql system stored procedure (protects you against sql injection attacks), better performance and less hardwork for your sql server.