Date field comparision - without time running slow

Question

Post reply

Date field comparision - without time running slow

Sam Krueger

SSC Enthusiast

Points: 164
More actions
January 20, 2006 at 8:54 am

#93177

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sam Krueger SSC Enthusiast Points: 164 More actions · Answer 2

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Using functions prevents index usage. That's why you're seeing the problem.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sam Krueger SSC Enthusiast Points: 164 More actions · Answer 6

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?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

>>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 ?

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)