February 4, 2009 at 2:52 am
Hi All,
I've tested this a fair bit and they seem to be the same as I would have expected.
but just to confirm .
Which statement in a where clause would you guess is better performing.
1
WHERE YEAR(date1) = YEAR(date2) + 1
2
WHERE date1 = DATEADD(y,1,date2)
I know some of you will point out that the results might be different cause the one is matching on dates and the other is matching on years, but lets say the results come out the same.
I would guess that number 2 is better slightly cause the indexes on date1 if any still hold as no functions are being applied
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2009 at 3:13 am
The only other thing I can think of is that YEAR is an int and it might be faster to compare and int than it is to compare datetime
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2009 at 3:56 am
Where is 'date2' coming from? Is that a variable?
It is not advisable to apply a function over a column in your WHERE clause. For example
WHERE date_column BETWEEN '2008-01-01' AND '2008-12-31'
might be much more efficient (in most cases) than
WHERE YEAR(date_column) = 2008
When you apply a function over a column, any index that exists on the column will become unusable. Assuming that the column date_column has an index, SQL Server NOT will be able to use it in the first example, but might use it in the second example.
.
February 4, 2009 at 3:59 am
Thanks Jacob,
Thats pretty much what I was thinking 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 4, 2009 at 4:25 am
You are welcome!
Cheers
.
February 4, 2009 at 4:43 am
The best would be to make a date dimension, with 1 row per day. Make in that dimension also day, month and year columns. If you use indexes well, it can be a nice performance boost for such queries.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply