April 1, 2005 at 9:44 am
I'm using this query to archive Event Data older than two weeks of this particular DB through DTS. Here's the query. Just curious, anyway to write this better? Thanks!
SELECT *
FROM events
WHERE DATEDIFF(DAY, EVENT_DATE, getdate()) > 14
Also, there is no Index on the field Event_Date.
April 1, 2005 at 9:53 am
If there's no index on EVENT_DATE, you're unlikely to do much better.
If there was an index, this might perform better, depending on the type of index and selectivity etc:
SELECT *
FROM events
WHERE EVENT_DATE < DATEADD(dd, -14, GETDATE())
April 1, 2005 at 12:01 pm
I would also put the value for DATEADD(dd, -14, GETDATE()) into a variable.
SELECT @MyCompareDate = DATEADD(dd, -14, GETDATE())
April 1, 2005 at 12:50 pm
This wouldn't be any faster than PW's solution. The advantage that PW's solution has is that an index can be used on the date because no modification has to be done before the comparaison... allowing the index seek to be executed instead of an automatic index scan.
April 1, 2005 at 2:15 pm
Thanks for all the quick replies! I just started at a new place and this is a 3rd party app they have, so I'll have to see whether or not I can add an Index on Event_Date to speed up the query. Either way, thanks for the suggestions.
Pat
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply