none
Index Scan & Index Seek

    Question

  •  

    Hi all,

     

    I'm curious on Index scan and index Seek on the following queries and the table TL2 had create a index on dtTransaction

     

    SQL 1

    SELECT DISTINCT TL1.*

    FROM Table1 TL1

    LEFT OUTER JOIN Table2  TL2 ON DATEADD(d,-1,TL2.dtTransaction) BETWEEN @tlBeginDate AND @tlEndDate

     

    This query gave me the index scan

     

    SQL 2

    SELECT DISTINCT TL1.*

    FROM Table1 TL1

    LEFT OUTER JOIN Table2  TL2 ON TL2.dtTransaction BETWEEN @tlBeginDate AND @tlEndDate

     

    This query gave me the index seek.

     

    By Using the execution plan,both index seek and scan give the same estimate cost operation in term of (I/O, CPU, Operator Cost, subtree)

    But the estimated Number of row, Row Size, Node Id is higher on the SQL1

     

    So may i know will this make any different in term of performance wise?

     

    Thanks for in advance.

    Thursday, October 23, 2008 3:53 AM

Answers

  • I would speculate that the optimizer decided that it couldn't scan the index because of the dateadd operation - you weren't comparing the indexed column using a straight >, <, >=, <=, =, <>, etc. type operator.  Try rewriting the query like this, and see if it results in a scan instead of a seek:

    SELECT DISTINCT TL1.*

    FROM Table1 TL1

    LEFT OUTER JOIN Table2  TL2 ON TL2.dtTransaction BETWEEN DATEADD(d, 1, @tlBeginDate) AND DATEADD(d,1,@tlEndDate)


    Let me know how you fare.

    Thursday, October 23, 2008 4:15 AM
    Moderator
  • Anytime you 'wrap' a column value with a function, it will negate the possibilitiy of an index seek, and usually requires either an index scan or a table scan.

     

    Think of it this way:

     

    You have an index on a column named dtTransaction, containing a date value. That index will be created using the actual values in the column..

     

    Now if you wanted to retrieve the rows where the dtTransaction value was one day later [ dateadd( 1, day, dtTransaction ) ], the query processor will have to add one day to each column value in order to determine if it meets the criteria. Since there is an index on dtTransaction, the query processor decides that it is most efficient to add one day to each dtTransaction value in the index -therefore an index scan.

     

    In your second query, the dtTransaction value is unaltered, so an index seek can be used.

     

    Normally, you want index seeks since that is usually the minimum I/O (reads).

    Thursday, October 23, 2008 4:22 AM
    Moderator

All replies

  • A scan can be very efficient, but only if the table is relatively small.  It involves each row in the table, whereas a seek efficiently traverses the index tree.  There are lots of articles on it - just google "Scan vs. Seek SQL Server".  Here's a particularily good one:

     

    http://blogs.msdn.com/craigfr/archive/2006/06/26/647852.aspx

     

    Does this answer your question?

    Thursday, October 23, 2008 3:57 AM
    Moderator
  • Hi Aaron,

     

    Thanks for your information.

     

    But i'm just curious why by adding DATEADD in the query make SQL to treat it as index scan, futhermore in excution plan it show that it actually using the right index that had created on dtTransaction.

     

     

    Thursday, October 23, 2008 4:05 AM
  • I would speculate that the optimizer decided that it couldn't scan the index because of the dateadd operation - you weren't comparing the indexed column using a straight >, <, >=, <=, =, <>, etc. type operator.  Try rewriting the query like this, and see if it results in a scan instead of a seek:

    SELECT DISTINCT TL1.*

    FROM Table1 TL1

    LEFT OUTER JOIN Table2  TL2 ON TL2.dtTransaction BETWEEN DATEADD(d, 1, @tlBeginDate) AND DATEADD(d,1,@tlEndDate)


    Let me know how you fare.

    Thursday, October 23, 2008 4:15 AM
    Moderator
  • If there is the case i'm got to change my logic to make it better Sad

     

    Thanks anyway

     

    Thursday, October 23, 2008 4:20 AM
  • Anytime you 'wrap' a column value with a function, it will negate the possibilitiy of an index seek, and usually requires either an index scan or a table scan.

     

    Think of it this way:

     

    You have an index on a column named dtTransaction, containing a date value. That index will be created using the actual values in the column..

     

    Now if you wanted to retrieve the rows where the dtTransaction value was one day later [ dateadd( 1, day, dtTransaction ) ], the query processor will have to add one day to each column value in order to determine if it meets the criteria. Since there is an index on dtTransaction, the query processor decides that it is most efficient to add one day to each dtTransaction value in the index -therefore an index scan.

     

    In your second query, the dtTransaction value is unaltered, so an index seek can be used.

     

    Normally, you want index seeks since that is usually the minimum I/O (reads).

    Thursday, October 23, 2008 4:22 AM
    Moderator
  • i'm new to indexing. i have a table with around 8 million rows. and i have no primary keys on this table. which type of indexing do you think i should use?  thanks a lot in advance
    Thursday, November 06, 2008 9:01 PM
  • As with everything in life, it depends ...

     

    It would be important to know the characteristics of the data, and how it may be queried in order to even start pointing you in a direction that might improve your situation.

     

    The first step is to explore using the 'Database Tuning Advisor'.

     

    Look at these resources:

     

    http://msdn.microsoft.com/en-us/library/ms166575.aspx

    http://msdn.microsoft.com/en-us/library/ms188639.aspx

    http://www.exforsys.com/tutorials/sql-server-2005/sql-server-database-tuning-advisor.html

    http://technet.microsoft.com/en-us/library/ms189303.aspx

    Thursday, November 06, 2008 10:07 PM
    Moderator