July 2, 2013 at 8:02 am
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.
http://msdn.microsoft.com/en-us/library/ms177416(v=sql.105).aspx
Anyone have any tips or tricks when using a datetime in a where clause?
July 2, 2013 at 8:29 am
Got nothing to do with the fact that it's a datetime.
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2013 at 10:18 am
Thanks, after reading your post, I added an option recompile to the select and that fixed my problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy