How to avoid index scans in SQL Server 2005?

Question

I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.

Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.

In my query I have to use the CellRow column in the WHERE clause like CellRow > 3 to get data from CellValue field. The problem is when I do it like this then it is always table scan that makes my query very slow.

I have also checked it by putting non-clustered index on CellRow field but still index scan because the CellRow field don't have so much values, it has only 1 to 5 values each time. I can't use CellID in the where clause.

I am using many tables in my query like

Table_1 is joining with table_2 by using clustered index column and table 2 is joining with table_3 by using non-clustered index column and table_3 is joining with Cell table by using non-clustered index.

The ending story is, I am getting different values from all the tables including cell table but when I use CellRow field in the WHERE clause, I am getting index scan.

Any solution :)

Thank you so much!

Answer 1

Any solution :)

No, not really.

As you said yourself - CellRow is just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each possible value.

SQL Server's query optimizer recognizes this and probably figures it's easier and more efficient to do a index scan rather than a seek for 20'000 rows.

The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2%, 3% or max. 5% of the rows for each query.

PS: Check your execution plan - does it get the values straight from the index, or does it need a "Key Lookup" step to go get the data??

You didn't mention what data types your columns are - if the CellValue isn't too big, you could add it to the index (or at least include it in the index) to avoid costly key lookups:

CREATE INDEX IX_CellRow_CellValues
ON dbo.Cell(CellRow) INCLUDE(CellValue)

You'd still have the index scan, though

Answer 2

create an index on cellrow, but the optimizer might decide not to use it if it only has 5 values, but if you know that values above 3 are rare, you could add a hint to the query to sort of make the engine use that index.
EDIT: Another more complex solution that may or may not work for you is to separate cellrows in different tables, and create a view that's the union of tables for cellrows 4 and 5, that way when you query on that view, it won't use an index, but it will only traverse the involved records. This is sort of what data dictionaries do, but be careful with this, as it involves more maintenance (say, the data changes from 3 to 4, that's not a single update anymore, but a delete from table '3' and insert in table '4', for example). Ultimately it depends on your needs. Usually this sort of things is done when you're talking of millions of records, but you can try and see if it does it for you.

Answer 3

I would expect WHERE CellRow in (4, 5) to work better than WHERE CellRow > 3. ----