Post reply

Query - literal date vs local variable

  • July 2, 2013 at 8:02 am

    #295229

    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

    #1629125

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 2, 2013 at 10:18 am

    #1629203

    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