0

Can anyone tell me why the first query executes a full table scan, while the second uses the index?

enter image description here

The table has a non-unique, non-clustered index on (ContractType, MaterialType). The table is denormalized, which is why I'm using nvarchars instead of foreign keys, and I'd like to keep it that way.

Tim_Cardwell
  • 236
  • 3
  • 15
  • 4
    Probably because it estimates that the first one will match a lot more rows and so it would have to do more lookups than in the second case. – Martin Smith Apr 25 '17 at 19:28
  • 1
    I agree with @MartinSmith. Take a look at the data in the table. How many rows match 'Price Only' vs 'Price'? – Chris Albert Apr 25 '17 at 19:30
  • That is true, however why would it do a full table scan? Why not simply use the index - would that not be faster? – Tim_Cardwell Apr 25 '17 at 19:30
  • No it wouldn't be faster unless the value is selective. Because it has to use the index then go back to the heap anyway to get the values for the columns not included in the index. The tipping point is generally [a very small percent of the table](https://www.sqlskills.com/blogs/kimberly/the-tipping-point-query-answers/) – Martin Smith Apr 25 '17 at 19:33
  • How is it going to get MatrixID? If that's not in the index it'll have to go to the table. – Matt Gibson Apr 25 '17 at 19:33
  • Alright so I know I can include in my index whatever columns I need to bring back, which will improve query performance. What I'm failing to understand is to how to create an index used to speed up specific WHERE clauses, such as the one above. My understanding with indexes, and selected columns that aren't covered in the index, is that a join would occur between the main table and the index. This join would truly be slower than a full table scan? – Tim_Cardwell Apr 25 '17 at 19:34
  • Share your execution plans using [Paste The Plan @ brentozar.com](https://www.brentozar.com/pastetheplan/) here are the instructions: [How to Use Paste the Plan](https://www.brentozar.com/pastetheplan/instructions/). – SqlZim Apr 25 '17 at 19:35
  • 1
    If they're not relevant to the lookup, you should use ["Included Columns"](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns)—that will keep your index lookups the same but the column won't need to be looked up in the table. However, I wouldn't start doing things like that until I had an actual performance problem. Don't, for example, think that a table scan is always a bad thing. – Matt Gibson Apr 25 '17 at 19:35
  • Why doesn't this table have a clustered index? – Martin Smith Apr 25 '17 at 19:37
  • Not having a clustered index is something I was toying around with. I'm actually adding one now. Give me a moment on the execution plan. – Tim_Cardwell Apr 25 '17 at 19:40
  • This table is ~1 million rows. However, ContractType and MaterialType will only ever have 3, and 4 distinct values respectively (i.e. 12 distinct combinations). I'm having trouble finding the article, but if I recall correctly, indexing columns with low cardinality doesn't really help you, which is what I suspect I'm seeing here. – Tim_Cardwell Apr 25 '17 at 19:46
  • Yep it doesn't help when the index is not covering but an index on `ContractType,MaterialType INCLUDE (MatrixId)` would work fine. – Martin Smith Apr 25 '17 at 19:49

1 Answers1

0

See here for the answer, as this question is basically a duplicate of it (I just didn't realize it at the time).

Does it make sense to use an index that will have a low cardinality?

Community
  • 1
  • 1
Tim_Cardwell
  • 236
  • 3
  • 15