I have a query that pulls invoice data. I'm looking for invoices with an invoice date > blah (where blah is the last date we processed invoices).
The query is long and complicated, and by the time i anonymized it, it probably wouldn't mean anything, so please excuse me if i don't post the code.
My problem comes down to a where clause on the invoice header file.
If i use a date literal:
AND h.invoice_date >= '2013-07-01'
The query performs great, if I use a local variable with the date:
AND h.invoice_date >= @MinInvoiceDateTime
I get a horrible query plan and rather than using the index on the invoice header table, the query scans a detail table index. I found an article on MSDN ( Optimizing Queries That Access Correlated datetime Columns ) , but that didn't help.
Anyone have any tips or tricks when using a datetime in a where clause?