Dynamic where clause

I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -

Method 1 -

exec usp_select @.whereCondition='col1 like ''abc%'' and col2 = ''xyz'''

In usp_select, I'll build and execute the sql like -

set @.sql = N'select * from table ' + @.whereConition

exec sp_executesql @.sql

(basically @.sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')

Method 2 -

exec usp_select @.whereCondition='col1 like @.p1 and col2 = @.p2', @.WhereParams='@.p1=abc%,@.p2=xyz'

In usp_select, I'll parse out the values in @.WhereParams and then build and execute the sql like -

set @.sql = N'declare @.p1 nvarchar(10),

@.p2 nvarchar(10);

set @.p1 = ''' + @.parsedValue1 + ''', @.p2 = ''' + @.parsedValue2 + '''; ' +

N'select col1 from table1 ' + @.whereCondition

exec (@.sql)

(basically @.sql becomes - declare @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.pt = 'abc%', @.p2 = 'xyz';

select col1 from table1 where col1 like @.p1 and col2 = @.p2)

Method 3 -

similar to Method 2 but exec(@.sql) will be structured to become -

exec(declare @.vparam nvarchar(100), @.p1 nvarchar(10), @.p2 nvarchar(10);

set @.vparam='@.p1 nvarchar(10), @.p2 nvarchar(10)'

set @.p1 = 'abc%', @.p2 = 'xyz';

execute sp_executesql N''select col1 from table1 where col1 like @.p1 and col2 = @.p2', @.vparam, @.p, @.p2)

When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure.

On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.

Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?

I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.

Any advice would be appreciated.

Yes. The Method-3 is recommanded to use.

The BOL says,

Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.

|||

What I don't understand is in Method2, the select part of @.sql is also static with parameters, why does it still result in a cache miss?

Why does sql profiler treat the entire @.sql string in Method2 as one sql statement but in Method3, it seems to treat it as two and result in both a cache miss and a cache hit? I am building @.sql the same way in both Method 2 and 3, the only difference is method 3 uses sp_executesql and the method 2 does not.

|||

LK,

1 - The optimizer could choose to not put the plan in cache, if it is sheap enough to compile it every time. Add event "SP:CacheInsert" to see if it is adding it.

2 - Even if it adds it, the batch is using variables in the expression on the "where" clause, so the query optimizer will not use the histogram (in case you have proper indexes for c1 and c2) from the index statistics to estimate cardinality, instead it will use the value of "All Density" associate with the group of columns. While using method 3, will use the histogram properly.

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

AMB

|||

Thank you. The articles are quite helpful too.