How to avoid slowdown on Sql Server 2005 with parameterized queries build from C #

Question

I am creating a complex query to show some statistics results in a web view. A view can have several different filters depending on the user's choice. In addition, it is possible to use wildcards.

I create this query programmatically in C # using SqlParameters. So the query looks like this:

sc.CommandText = "SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2"; //...and more parameters sc.SqlParameters.Add( new SqlParameter("@filter1", SqlDbType.Int, 32) { Value = 1}); sc.SqlParameters.Add( new SqlParameter("@filter2", SqlDbType.VarChar, 446) { Value = "whatever%"}); 

This is a very simplified version, but the request itself is not the same. Just keep in mind that it may have different optional parameters (which, I think, is a fairly common situation).

When I ran this query in Sql Manager, I realized that when using the parameters, there is a significant slowdown. Thus, the following two queries must be the same, they use a different execution plan, which forces one run to be parameterized much slower:

 DECLARE @filter1 INT DECLARE @filter2 VARCHAR 446 SET @filter1 = 1 SET @filter2 = "whatever%" SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2 

Quick version:

 SELECT * FROM table WHERE field1 = 1 AND field2 LIKE 'whatever%' 

Here is another example with the same problem:

Why a parameterized query produces a significantly slower query plan versus an unparameterized query

It looks like there is something called a sniffing parameter that can slow down the execution of a parameterized query, but in my case this does not apply because it is not a stored procedure.

One suggestion is to use OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR). I can’t do this because I have about 10 optional parameters that can be in the filter or not, and this parameter does not work when using LIKE .

So, I feel like I'm at a standstill, and I'm thinking of getting rid of the parameters and building dynamic literals in the code. But then Sql Injection comes into play.

So, do you have any other suggestions for solving this problem? Or do you know a safe way to avoid options?

EDIT . Here you can see plans for executing a query with one parameter using LIKE :

• Execution plan

EDIT : A more simplified sample query execution plan:

• Simplified Implementation Plan

Answer 1

Take a look at the Estimated Row Count property in the execution plan. With your slow version (with parameters), SQL Server cannot evaluate the rows returned by your query well, because it will not evaluate the actual value of the variables at compile time. It simply uses statistics to estimate the power of the fields that you use as filters, and create a plan of execution in accordance with it.

My solution to this problem was to create a stored procedure with as many parameters as the filters you want:

 CREATE PROCEDURE your_sp @filter1 INT, @filter2 VARCHAR(446) AS SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2 

---

 sc.CommandText = "your_sp"; sc.CommandType = CommandType.StoredProcedure; sc.SqlParameters.Add(new SqlParameter("@filter1", SqlDbType.Int, 32) { Value = 1}); sc.SqlParameters.Add(new SqlParameter("@filter2", SqlDbType.VarChar, 446) { Value = "whatever%"}); connection.Open(); SqlDataReader reader = command.ExecuteReader();