DATEADD ISSUES

[talenx]

I have an odd issue (mainly revolving around proper index usage.)
For some reason when I use a DATEADD function within a join the between or >= , <= operators the optimizer ignores the index and performs a full table scan.

Example
This will NOT use the INDEX

--code start
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0, DATEDIFF(MONTH, -1, GETDATE())) AND  DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
--code end

Basically the query will return dates today and a 1 month ago. which works fine but in looking at the query analyzer I find the optimizer is not using the index where as if i had used a hard coded date or DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) without supplying DATEADD range.

This WILL use the INDEX

--CODE START
Select * from Time_Dimension where Date_Number BETWEEN DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) AND  DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
--CODE END

OR

--CODE START
Select * from Time_Dimension where Date_Number BETWEEN '2/1/2005' AND '2/28/2005'
--CODE END

OR EVEN.. ( AND THIS ONE IS WEIRD)

--CODE START
Select * from Time_Dimension where Date_Number DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

Any Thoughts
Thanks
TalenX

 

[Walid]

Try to calculate the variables before the query

DECLARE myDate AS DATATIME
SET myDate………….

Then feed these variables into the query and see if the optimizer will use the index


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[vongrunt]

> Example
> This will NOT use the INDEX

Check first part:
select DATEADD(DAY, 0, DATEDIFF(MONTH, -1, GETDATE()))

Year 1903, right?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[talenx]

Oh, sorry I just used that conversion to show that the dateadd function invalidates the index ... at least in the optimizers eyes..

If you want you can use the following:

select dateadd(d, -1, cast(cast(month(getdate()) as varchar)+'/01/'+cast(year(getdate()) as varchar)  as datetime))

 

[talenx]

Walid, unfortunately where this SQL is being used it can not use a variable. I know.. I know that sucks... gotta love working with third party software that only supports ansi 92 code.

vongrunt,
ya, sorry bout that...
You know the odd thing is if you were to try the code with getdate() the optimizer uses the index ... but once the dateadd function is used ( with a variable ) is performs a full table scan.

 

[vongrunt]

Another odd thing: specify WITH(INDEX(datecolindexname)) hint. Exec plan will look ugly (thick arrows) - and query finish instantly. True?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[talenx]

Yep! It’s almost as if the optimizer completely ignores the index when ANY function is applied to a data object... can that be right? Even if I cast or convert the data object to the proper data type (date time, in this case) the optimizer still ignores it.