You are here

SQL: slow query with parameters

In my C# reporting program I had a query which was giving me timeouts. The part that gave me trouble was something similar to:

SELECT *

FROM invoices

WHERE processdate BETWEEN @from AND @until
AND printdate > accountingdate

If I defined the @from and @until variables in the query, i.e. if I prepend it with:

DECLARE @from datetime = '2015-01-08'
DECLARE @until datetime = '2015-01-14'

everything ran very fast as expected.

But when I run the query from my C# program the parameters get added like this:

SqlCommand sqlCommand = new SqlCommand(queryString, sqlConnection);
sqlCommand.Parameters.AddWithValue("@from", fromDate);
sqlCommand.Parameters.AddWithValue("@until", untilDate);

Apparently this way the SQL server doesn't know anything about the contents of the variables and compiles the query to evaluate the "printdate > accountingdate" clause first. This turns out to be a bad choice and the query runs very slow.

At http://sqlmag.com/sql-server/using-recompile-query-hint-solve-parameter-... I learned that I can give the server some optimization hints by giving fixed values to use during the compilation:

SELECT *

FROM invoices

WHERE processdate BETWEEN @from AND @until
AND printdate > accountingdate

OPTION(OPTIMIZE FOR (@from = '2015-01-01', @until = '2015-01-01'))

This way, the "processdate BETWEEN @from AND @until" clause gets evaluated first and the query runs fast again.