How to avoid slow down on Sql Server 2005 parameterized queries build from C#

I'm building a complex query to show some statistics results in a web view. The view can have several different filters depending on the user's choice. Also, there is the possibility to use wildcards.

I'm building this query programatically 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 query itself is not the point. Just keep in mind that it can have different optional parameters (which I think it is a pretty common situation).

When I ran this query in Sql Manager I realized that there is a huge slow down when using parameters.So, the following two queries, that should be the same, they use a different execution plan that makes the parameterized one run a lot slower:

DECLARE @filter1 INT
DECLARE @filter2 VARCHAR 446
SET @filter1 = 1
SET @filter2 = "whatever%"

SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2

The fast version:

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

Here is another example of someone with the same issue:

Why does a parameterized query produces vastly slower query plan vs non-parameterized query

Seems that there is something called parameter sniffing, that might make a parameterized query run slower, but it does not apply in my case because this is not a stored procedure.

One of the solutions proposed is to use OPTION(RECOMPILE) or OPTION(OPTIMIZE FOR). I can't do that because I have about 10 optional parameters, that may be in the filter or not, and this option is not working when using a LIKE.

So, I feel I'm in a dead end and I'm thinking on get rid of the parameters and build dynamic literal queries on the code. But then Sql Injection comes in the game.

So, do you have any other suggestions on how to solve this issue? Or do you know a safe way to escape the parameters?

EDIT: Here you can see the execution plans for a query with one parameter using 开发者_运维问答LIKE:

• Execution Plan

EDIT: A more simplified representative query execution plan:

• Simplified execution plan

---

Take a look at the "Estimated number of rows" property in the execution plan. With your slow version (with parameters), SQL Server is not able to make a good estimation of the rows that your query will return, because it won't evaluate the actual value of the variables in compilation time. It will just make use of the statistics to estimate the cardinality of those fields you are using as filters, and create an execution plan according to it.

My solution to a problem like this one, was creating a stored procedure with as many parameters as 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();

---

Sometimes queries requiring filtered indexes can cause problems.

I just had a situation where I had a query something like

orders.Where(x => x.Cancelled == options.isCancelled)

Where options.isCancelled was a dynamic boolean. This became parameterized in the SQL query with EFCore to something like SELECT ... FROM Orders WHERE cancelled = @param_cancelled. The database can't use filtered indexes because it doesn't know in advance what the value would be.

The solution for me was:

if (options.isCancelled) 
{
   orders = orders.Where(o => o.Cancelled == true);
}
else 
{
   orders = orders.Where(o => o.Cancelled == false);
}

This enabled the query optimizer to use a filtered index I had created:

`(WHERE IsCancelled = 1)`. 

This index greatly improved the performance.

TBH something fishy was still going on because it worked fine in SSMS even without the index but was completely timing out in C#. This trick forced two different distinct queries which SQL Server would need to find two independent query plans for. So this at least made me confident to proceed even though I still wasn't 100% sure what happened.