Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Oct 24 '11 at 22:21

This question came from our site for professional and enthusiast programmers.

1  
Please post your CREATE TABLE batches for these tables. Your description is very hard to follow. –  Thomas Stringer Oct 24 '11 at 21:13
6  
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 value. SQL Server's query optimizer probably recognizes this and figures it's easier and more efficient to do a index scan. The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2% or max. 5% of the rows for each query –  marc_s Oct 24 '11 at 21:13
    
@marc_s +1, that should be the answer. SQL Server uses a certain parameter to determine when a seek is more costly than a scan (and there is a line there). You have the right answer. –  Thomas Stringer Oct 24 '11 at 21:15
3  
You could also consider making a covering NCI or changing the clustered index to be on CellRow, Cell_ID to benefit this type of range query (and adding a new unique NCI for Cell_ID) –  Martin Smith Oct 24 '11 at 21:17
    
why not add an index on CellRow if you want to filter by that. –  CBRRacer Oct 24 '11 at 21:19

3 Answers 3

up vote 11 down vote accepted

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
4  
welcome to dba.se and thanks for your answer. Would you be willing to explain in a bit more detail your reasons for expecting that? –  Jack Douglas Nov 18 '11 at 18:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.