SQL Server Performance

Index on DateTime

Discussion in 'General DBA Questions' started by whygh, Mar 21, 2007.

  1. whygh New Member

    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.

  2. joechang New Member

    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'
  3. MohammedU New Member

    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
  4. whygh New Member

    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


  5. joechang New Member

    yes but compare the relative plan cost and estimated rows for each of the last 2
  6. joechang New Member

    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()

  7. mmarovic Active Member

    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.

Share This Page