Index on DateTime

Hi everyone,

Good morning.

One table has around 100 million rows.two indexes: one is clustered index and another is non clustered index on datetime fields.

create table myTable(

action_id int, Clustered index, primary key
website_id int,
channel_id int,
session_id int,
capture_date datetime non-clustered index
)

I created the query:

The first way:
select action_id, website_id, channel_id, session_id, capture_date from MyTable
with (nolock) where convert(varchar(10),capture_date,20) = convert(varchar(10),dateadd(d,-1,getdate()),20)

The Second Way:
select action_id, website_id, channel_id, session_id, capture_date from MyTable
with (nolock)
where capture_date > convert(varchar(10),dateadd(d,-1,getdate()),20) and
capture_date<convert(varchar(10),getdate(),20)

The execution plan of first one is using "index scan" on Capture_date

The execution plan of Second one is using "Clustered index scan".

I wonder why the second one is not using index on capture_date.

probably because the estimate row is high enough that the optimizer does a table scan

for better statistics estimate try:

select action_id, website_id, channel_id, session_id, capture_date
from MyTable with (nolock)
where capture_date > '2007-03-20' and capture_date < '2007-03-21'

if the plan for the above is still a table scan
compare the above plan with the plan for:

select action_id, website_id, channel_id, session_id, capture_date
from MyTable with (nolock, index('name of your nonclustered index'))
where capture_date > '2007-03-20' and capture_date < '2007-03-21'

Your first query using Index Scan is expected because you are converting the column...
How often you update the statistic on this table?
Update the statistics on this table and run the query without forcing the index...if it still doing cluster index scan then try forcing the index as Joe suggested...

How many rows your query suppose to return?

MohammedU.
Moderator
SQL-Server-Performance.com

Thanks Reply.
What I am doing :

select action_id, website_id, channel_id, session_id, capture_date
from MyTable with (nolock)
where capture_date > '2007-03-20' and capture_date < '2007-03-21'

Using Capture_date Index Seek

select action_id, website_id, channel_id, session_id, capture_date
from MyTable with (nolock)
where capture_date > convert(varchar(10),dateadd(d,-1,getdate()),20) and
capture_date<convert(varchar(10),getdate(),20)

Using Clustered index scan before and after update statistics



select action_id, website_id, channel_id, session_id, capture_date
from MyTable with (nolock,index(IX_Capture_date))
where capture_date > convert(varchar(10),dateadd(d,-1,getdate()),20) and
capture_date<convert(varchar(10),getdate(),20)


Using Capture_date Index Seek

yes but compare the relative plan cost and estimated rows for each of the last 2

I am going to guess that with
where capture_date > '2007-03-20' and capture_date < '2007-03-21'

the optimizer is using the actuall statistics distribution for the above dates

but with
where capture_date > convert(varchar(10),dateadd(d,-1,getdate()),20) and
capture_date<convert(varchar(10),getdate(),20)

its not evaluating convert(varchar(10),getdate(),20)
it just using the default assumption of 1/10 the total rows for > & <

also, why are you doing convert
why not just getdate()

If you package your query into procedure with @dateFrom, @dateTo parameters you have better chance of using optimal execution plan. When the procedure is first executed, exec plan based on passed parameter values will be compiled using actual statistics. Also, as Joe mentioned, there is no need for conversions, just use getDate() and getDate()-1 (or dateAdd function) when assigning values to sp parameters. For more hints about queries based on dateTime values find Frank's (Frank Kalis) article on this site.