Index with Leftouter join there is always Index scan in sql server 2005

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. :)

---

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.

链接地址: http://www.djcxy.com/p/44008.html

上一篇: 按连接表上的性能问题排序

下一篇: 索引与Leftouter加入在SQL Server 2005中总是有索引扫描