.NET Table Adapter Select query is slow when using variable only in application

I am using the .NET TableAdapter to execute a SELECT query in my application, as shown below. The problem is this type of queries sometimes takes several minutes to execute. However, while I tested it in the SQL server, it returns the result in a flash. (Doing SET ARITHABORT ON/OFF and OPTION(RECOMPILE) could fix the problem sometimes, but not always.)

/*The query defined in the designer.cs*/
SELECT Column1, Column2, Column3...
FROM MyTable
WHERE column1 = @var1 AND column2 = @var2

If I remove the variables to use literals, it fixes the speed.

SELECT Column1, Column2, Column3...
FROM MyTable
WHERE column1 = 'Billing' AND column2 = 'New'

Using SqlCommand.ExecuteReader() does not work as well. I think it still sends the parameters as separate variables, for SQL Server to process.

Finally, I tried to use variable in SQL Server, but it does not slow down any speed. Hence, I wonder if using variable is actually the problem. (shouldn't TableAdapter replace the variables with literals before sending the query?)

DECLARE @status va1= 'Billing'
DECLARE @status var2= 'New'

SELECT Column1, Column2, Column3...
FROM MyTable
WHERE column1 = @var1 AND column2 = @var2

Is there way to get the performance fixed, without changing all queries with variables to literals?

Solution

When you use variables like @var1 and @var2 in your query, SQL Server must generate a query execution plan that considers the potential values of these variables. SQL Server tries to create a plan that is suitable for various parameter values. This process can lead to a suboptimal execution plan in some cases, especially if the distribution of data in your table is uneven.

On the other hand, when you use literals like 'Billing' and 'New' in your query, SQL Server can generate a query execution plan that is specific to these constant values. This can result in a more efficient execution plan because it doesn't have to consider different possible values.

My suggestions;

Ensure that the statistics for the columns involved in your query are up to date. Outdated statistics can lead to suboptimal query plans. You can use the UPDATE STATISTICS command to update statistics for specific tables and columns.

UPDATE STATISTICS MyTable

Also you can clear the cache for a specific query using DBCC FREEPROCCACHE, but use this carefully as it can affect overall server performance.cached query plans can become less efficient.