January 20, 2006 at 8:54 am
I am having some weird performance hits when comparing date fields - I want to compare dates only and not the time portion of the field. Hopefully someone can educate me as to why and give a possible solution.
I have a table with a datetime field. When I view the data in Query Analyzer it shows this field as
11/12/2005 9:55:01 AM
What I am trying to do is select all records from this table where records are greater than today minus 7 days (without comparing the time portion of the field).
Example:
Select * from table where activitydate > Convert(datetime, Convert(int,DATEADD(day, -7, getdate())))
This works, but it takes 93 seconds for the query to run.
Example2:
select * from table where DATEDIFF(day, C_Tracked_Item_Hist.ActivityDate, getdate()) <=7
This works, but it takes 38 seconsds to for the query to run
If I manually run this query, it runs in under a second:
Select * from table where activitydate > '13-Jan-2006'
No matter what I am trying, everything is slow.
If I try and reproduce the exact date format entered manually in the above query that runs fast, it runs slow 93 seconds - same as the 1st query
example
DECLARE
@DT DATETIME,
@DE char(11)
SET @dt = DATEADD(day, -7, getdate())
SET @de = DATENAME(DAY,@DT) + '-' + LEFT(DATENAME(MONTH,@DT),3) + '-' + DATENAME(YEAR, @dt)
Select * from table where activitydate > @de
January 20, 2006 at 10:05 am
create an index on the column and see how that affects the speed:
CREATE INDEX IX_ActivityDate on C_Tracked_Item_Hist(ActivityDate);
also throw away all your converts...
Select * from table where activitydate > DATEADD(day, -7, getdate())
Lowell
January 20, 2006 at 10:08 am
Field alredy has an index. If I do the following it runs fast, but I would like an alternate/better approach...
DECLARE
@sqlstr nvarchar(4000),
@DT DATETIME,
@DE char(13)
SET @dt = DATEADD(day, -7, getdate())
SET @de = '''' + DATENAME(DAY,@DT) + '-' + LEFT(DATENAME(MONTH,@DT),3) + '-' + DATENAME(YEAR, @dt) + ''''
set @sqlstr = n'Select * from table where activitydate > ' + @de
exec sp_executesql @sqlstr
go
January 20, 2006 at 10:11 am
Using functions prevents index usage. That's why you're seeing the problem.
January 20, 2006 at 10:13 am
Pre-compute the required selection date into a variable and use that. Should allow optimizer to use the index:
Declare @DateFrom As SmallDateTime
Select @DateFrom = Convert(Smalldatetime, Convert(int,DATEADD(day, -7, getdate())))
Select * from table where activitydate > @DateFrom
January 20, 2006 at 10:17 am
i think you might be getting hung up on formatting...
remember the date is actually stored as a double,where the integer portion is days and the decimal portion it part of a day; but displayed as a date...so you are trying to format the where clause to match the display, instead of the real datetime value; just use datediff and forget trying to parse out month/day/year.
select * from C_Tracked_Item_Hist
where datediff(d,ActivityDate,getdate()) > 30
select convert(decimal(16,4),getdate())=38735.5108
select convert(decimal,getdate())=38736
Lowell
January 20, 2006 at 10:25 am
PW Your solution takes longer - 202 seconds.
Lowel - I did try datediff and it takes 93 seconds -
your example (takes 93 seconds)
select * from C_Tracked_Item_Hist
where datediff(d,ActivityDate,getdate()) > 7
my fast example (takes milliseconds)
select * from C_Tracked_Item_Hist
where ActivityDate>'13-Jan-2006'
Why would this be?
January 20, 2006 at 10:32 am
>>Why would this be?
Check the execution plan in each case (highlight the SQL and hit CTRL-L in query analyser).
Something is causing the optimiser not to use the index. This occurs when you use a function or expression in the WHERE clause. DateDiff() is a function.
Is the column a smalldatetime or datetime in the table ?
January 21, 2006 at 11:57 pm
What is the datatype of the column you call "Activity Date"? I have the sneaky suspicion that it's not a DateTime column and need to know what datatype it is before I can help.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply