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.

Comments

Hi there, just became alert to your blog through Google, and found
that it's truly informative. I'm going to watch out for brussels.

I will appreciate if you continue this in future.

A lot of people will be benefited from your writing.
Cheers!