Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query - literal date vs local variable Expand / Collapse
Author
Message
Posted Tuesday, July 02, 2013 8:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:13 AM
Points: 92, Visits: 632
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?




Post #1469523
Posted Tuesday, July 02, 2013 8:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1469557
Posted Tuesday, July 02, 2013 10:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:13 AM
Points: 92, Visits: 632
Thanks, after reading your post, I added an option recompile to the select and that fixed my problem.
Post #1469650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse