title: MSSQL query runs slow with lot of LEFT JOIN
body: <p>I am working with mssql server 2008r2. 
  I have a huge tables which contain 19 stores turnover data day by day item by item. It has some 100million rows. But with the partitioned view it is easy to run queries.</p>
  
  <p>The structure of the table looks like this:</p>
  
  <ol>
  <li>Store name</li>
  <li>Item id</li>
  <li>Date</li>
  <li>Turnover</li>
  <li>Selled quantity</li>
  </ol>
  
  <p>The output report looks like this:
  Item id, date, store_1.turnover, store_1.quantity, store_2.turnover, store_2.quantity....</p>
  
  <p>To create this kind of report i use this query:</p>
  
  <pre><code>WITH cte_sell AS(
      SELECT * 
      FROM view_turnover 
      WHERE date BETWEEN '130413' and '130418' )
  
  SELECT a.item_id,a.date,
      store_1.turnover,store_1.quantity,
      store_2.turnover,store_2.quantity,
      store_3.turnover,store_3.quantity
  
  FROM (SELECT DISTINCT item_id,date FROM cte_sell) as a
  LEFT JOIN (SELECT * FROM cte_sell WHERE store='Store 1') as Store_1 ON a.item_id=store_1.item_id and a.date=store_1.date    
  LEFT JOIN (SELECT * FROM cte_sell WHERE store='Store 2') as Store_2 ON a.item_id=store_2.item_id and a.date=store_2.date    
  LEFT JOIN (SELECT * FROM cte_sell WHERE store='Store 3') as Store_3 ON a.item_id=store_3.item_id and a.date=store_3.date
  </code></pre>
  
  <p>I hope you can understand the query.</p>
  
  <p>The query runs less then 10 second for 14-15 store (14 15 LEFT JOIN), which is good.
  But the problem is when I select all the 19 store (19 LEFT JOIN) the query slows dramatically. It can take 2 or 3 minutes to finish.</p>
  
  <p>The creation of the execution plan is much longer too:</p>
  
  <ul>
  <li>For 12 store only 2 seconds</li>
  <li>For 19 store 22 seconds</li>
  </ul>
  
  <p>What do you think, are there any limitation of tables joining? 
  I think there are some server parameter which controls the huge queries.
  Has anyone any idea how to optimize the query or the server?</p>
  
  <p>The worst thing, the server can freeze sometimes: I created a trace file while the query was run at 9 o'clock and at 12 o'clock. At 9 it took 2 minutes to run the query. 
  At 12 I had tor restart the comupetr after 10 minutes, because I can't restart the SQL service from the Configuration manager.</p>
  
  <p>I can't attach screnshots.</p>
  
  <p>In the activity monitor the process is in suspended state. 
  The Wait Type is: CXPACKET
  The Wait Resource is: exchang....
  Sorry, but I don't know much about locks.</p>
  
  <p>I tried to connect from the management studio and I get this message: The connection was succesfully established with the server, but then an error occured during the login process. (Provider: TCP provider, error:0 (Translate from Hungarian): The existing connection was forced to close by the remote machine.</p>