Optional Search Parameters with SQL Server 2008 R2


We can have a compact solution to skip some search conditions in a where clause but this query can become a victim of Parameter Sniffing with degraded performance.

For this reason, there is a way to force the query to recompile each time at the end of the WHERE clause to provide an improved performance boost in SQL Server 2008 R2:

WHERE (col1 =@p1 OR @p1 IS NULL)
AND  (col2 =@p1 OR @p2 IS NULL)
AND  (col1 =@p13 OR @p3 IS NULL)
ORDER  BY  col1
OPTION (RECOMPILE)

 

If you are intertest in this topic, you can find this link for other solutions too:

http://www.sommarskog.se/dyn-search-2008.html

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s