SQL Server Performance

Indexing Low-Selectivity Columns

Discussion in 'Performance-Related Article Discussions' started by shanetasker, Jun 9, 2006.

  1. shanetasker New Member

  2. gaurav_bindlish New Member

    There is a major problem with this approach. You have not considered the time taken for compilation of the plan. In my exeriments, I have observed that the query compilation cost increases very steeply as more and more indexed views are created.

    Gaurav
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    http://blogs.msdn.com/gauravbi/default.aspx
  3. Chan New Member

    Nice article but I must agree with Gaurav's comments on the maintainability and creation of Indexed Views. I have always used Partitioned Tables to solve this particular problem which would work quite well in the example that you gave but you would need to be very careful and not go too crazy when partitioning data.
  4. merrillaldrich New Member

    Great comments - I'll test those two things for comparison and report back what I find.
  5. RogerDahl New Member

    Hello there,

    Thank you for the article.

    Wouldn't a clustered index take care of this entire problem? It seems to me that with a clustered index, SQL Server wouldn't have to scan the entire table, even when the index has low selectivity.

    Thanks,

    Roger
  6. merrillaldrich New Member

    Hi -<br /><br />The scenario I tried to lay out I hope implies that the table would be used for other purposes, in addition to the specific query being optimized -- in other words, its part of a bigger system. Also, I hope its implied that a real table exhibiting this problem would be more complex than the artificially simple sample I used. With that in mind, I feel like the selection of a clustered index for the table would be subject to other considerations and constraints, and it seems like clustering on the bit columns is not likely to be the best choice in that context.<br /><br />But, to be sure, I did not explain all that background <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />
  7. RogerDahl New Member

    Thank you for the clarification, merrillaldrich.

    Roger

Share This Page