0

I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected.

For example, with the query below I would expect a clustered index seek on the table:

SELECT id FROM dbo.TableName WHERE id = 256

Whereas when I use the view I get the clustered index seek on the table along with 3 nested loops (left outer join).

SELECT id FROM dbo.ViewName WHERE id = 256

Why would this occur? The above is a very simple example but when selecting additional columns from the view more and more unnecessary LEFT JOINS appear in the execution plan.

  • 2
    We'll need the queries and execution plans to answer this. – Mark Storey-Smith Oct 19 '14 at 11:57
  • 2
    Related question on SO. Joining 100 tables – Martin Smith Oct 19 '14 at 12:00
  • Can you post a sample view and query from the view that demonstrates the problem? – Dan Guzman Oct 20 '14 at 5:37
5

The link provided by Martin Smith to an existing SO question provided me with the answer:

The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.