title: Why does adding just one more INNER JOIN to a FROM clause make my SQL query so slow?
body: <p>I'm working on a MS SQL 2008 database that came with a view called <code>AcmeView</code>.  When I script <code>AcmeView</code>, I see that it joins together three other views: <code>View1</code>, <code>View2</code>, and <code>View3</code>.  A coworker asked if I could compose a view that would combine the contents of <code>AcmeView</code> with data from another view: <code>NewView</code>.</p>
  
  <p><code>AcmeView</code> returns a result set in three seconds; it's constituent views contain tens of thousands of rows each.</p>
  
  <p><code>NewView</code> is a much smaller view; it returns only about two thousand rows each; and it returns its result set in a small fraction of a second.</p>
  
  <p>Therefore, I was disappointed that when I modified <code>AcmeView</code> to INNER JOIN with <code>NewView</code>, the resulting view takes almost twenty seconds to return its result set.  What could possibly be wrong with the view to make it so slow?</p>
  
  <p>The SELECT statement for <code>AcmeView</code> looks like this:</p>
  
  <pre><code>SELECT *
  FROM
      View1
      INNER JOIN View2
          ON View2.foo = View1.foo
      INNER JOIN View3
          ON View3.bar = View2.bar    
      -- Executes in 3 seconds
  
  SELECT *
  FROM
      View1
      INNER JOIN View2
          ON View2.foo = View1.foo
      INNER JOIN View3
          ON View3.bar = View2.bar
      INNER JOIN NewView
          ON NewView.qwerty = View1.querty
      -- Executes in 20 seconds; why so long?
  </code></pre>
  
  <p>How can it be that adding in just one more view, <code>NewView</code> to my FROM clause slows the query to a crawl and what can I do about it?  Once again <code>NewView</code> can be queried in a small fraction of a second.</p>