Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    9

    Unanswered: Index Scan vs Index Seek

    I have a really strange problem.

    I execute this query:

    declare @cid int
    set @cid = 2003227

    select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid

    select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @cid or @cid = 0

    3 Million rows in sales, 120000 in product.

    The first does and index seek, the second an index scan.
    The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13%

    This problem obviously gets worse the bigger the dataset / query /etc.

    The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix?

    Any help would be appreciated.

    Josh

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i believe that the 'OR' statement is causing a table scan(the leaf level of the clustered index)

    consider this
    i am going to assume that only one of these columns are indexed.
    try creating an index on the other column, because if you query a table and use the 'OR' clause in your query and any of the columns are not indexed.... SQL server will perform a table scan.

  3. #3
    Join Date
    Jul 2002
    Posts
    9
    Okay, If I simplify the queries to this:

    declare @c_ID int
    set @c_Id = 2003227

    select * from sales where customer_id = @C_ID
    select * from sales where customer_id = @C_ID or @c_Id = 0

    (There is a non-unique, non clustered index on the customer_Id column.)

    The problem still happens. My question is - why does it use an index scan for the second query, and why does it take so damn long?

    Josh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •