February 27, 2009 at 12:24 pm
We have a query that is generated to get records for a particular day, and the part of the WHERE clause that is generated looks like so:
(([T_1].[Date] < '2/27/2009 12:00:00.000 AM') AND ([T_1].[Date] > '2/25/2009 11:59:59.999 PM'))
With it like this, the query takes around 30 seconds to run!
Changing the query to look like this:
(
(YEAR([T_1].[Date]) = 2009)
AND
(MONTH([T_1].[Date]) = 2)
AND
(DAY([T_1].[Date]) = 26)
)
allows it to run in under 10 seconds (still not what we're hoping for, but more acceptable..).
This problem definitely seems to be more prevalent when pulling up records with recent dates rather than older dates. Also, we seem to be having the issue across different tables.
We're thinking about changing the generated WHERE clause as a first step, but also hoping to understand better what's happening here. Can anyone provide insight as to why specifying the WHERE clause differently makes such an impact? Isn't SQL Server touching the dates the same anyway?
Also, any guidance on indexing in regards to this would be much appreciated. The tables involved are NOT indexed on the date fields.
Thanks for your time.
February 27, 2009 at 12:42 pm
Check out the execution plan of both queries and execute them both a couple of times (so data will be in memory).
(SSMS Querypanel Ctrl+M or Menu Query topy "Include actual execution plan")
If no date index available, it will need a full table scan, and my need much mor IO to get to the data.
Also when using datetime input values, provide them in conjunction with a convert statement to provide the datetime format to be used.
e.g.
where mydatecol > convert(datetime,'2009-04-01',121)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply