I was asked to look at a query today that was taking a very long time to return. I waited 10 minutes and nothing was happening. It wasn't blocked by any other process (in fact the query was running against a backup of the live database and therefore nothing else was happening in the database). The query plan also looked fine - no table scans or index scans and no suggested missing indexes.
The query was filtered on a date field like so:
a.actDateAdded between DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) and
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
In an effort to improve the performance, I changed this to use parameters instead and set the parameters to the exact same values as above:
a.actDateAdded between @start and @end
This time the query ran in less than a minute.
So my question is, why the dramatic improvement?