Question

I have query joining several tables, the last table is joined with LEFT JOIN. The last table has more then million rows and execution plan shows table scan on it. I have indexed columns on which the join is made. It is always use index scan but If I replace LEFT JOIN with INNER JOIN, index seek is used used and execution takes few seconds but with LEFT JOIN there is a table scan , so the execution takes several minutes. Does using outer joins turn off indexes? Missed I something? What is the reason for such behavior? Here is the Query

Select * FROM

     Subjects                  s
    INNER join       Question  q ON q.SubjectID   = s.SubjectID
    INNER JOIN       Answer    c ON a.QestionID   = q.QuestionID
    Left outer JOIN  Cell      c ON c.Question ID = q.QuestionID

Where S.SubjectID =15

There is cluster index on SubjectID in "Subject" table. and there is non-cluster index on questionID in other tables.

Solution: I try it in other way and now I am index seek on Cell table. Here is the modified query:

Select * FROM

     Subjects                  s
    INNER join       Question  q ON q.SubjectID   = s.SubjectID
    INNER JOIN       Answer    c ON a.QestionID   = q.QuestionID
    Left outer JOIN  Cell      c ON c.Question ID = q.QuestionID
                                              AND C.QuestionID > 0
                                              AND C.CellKey > 0

Where S.SubjectID =15

This way I did high selectivity on Cell table. :)

Answer 1

I just tried to simulate the same issue, however there is no table scan instead it was using the clustered index of Cell, at the same time you could try to force the index, you can check the syntax here and the issues you may face when forcing an index here. Hope this helps.

友情链接