var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
i believe that the 'OR' statement is causing a table scan(the leaf level of the clustered index)
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.
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?