title: How order of joins affect performance of a query
body: <p>I'm experiencing big differences in timeperformance in my query, and it seems the order of which the joins (inner and left outer) occur in the query makes all the difference.
  Are there some "ground rules" in what order joins should be in?</p>
  
  <p>Both of them are part of a bigger query.
  The difference between them is that the left join is placed last in the faster query.</p>
  
  <p>Slow query: (> 10 minutes)</p>
  
  <pre><code>SELECT [t0].[Ref], [t1].[Key], [t1].[Name],  
      (CASE 
          WHEN [t3].[test] IS NULL THEN CONVERT(NVarChar(250),@p0)
          ELSE CONVERT(NVarChar(250),[t3].[Key])
       END) AS [value], 
      (CASE 
          WHEN 0 = 1 THEN CONVERT(NVarChar(250),@p1)
          ELSE CONVERT(NVarChar(250),[t4].[Key])
       END) AS [value2]
  
  FROM [dbo].[tblA] AS [t0]
  INNER JOIN [dbo].[tblB] AS [t1] ON [t0].[RefB] = [t1].[Ref]
  
  LEFT OUTER JOIN (
      SELECT 1 AS [test], [t2].[Ref], [t2].[Key]
      FROM [dbo].[tblC] AS [t2]
      ) AS [t3] ON [t0].[RefC] = ([t3].[Ref])
  
  INNER JOIN [dbo].[tblD] AS [t4] ON [t0].[RefD] = ([t4].[Ref])
  </code></pre>
  
  <p>Faster query: (~ 30 seconds)</p>
  
  <pre><code>SELECT [t0].[Ref], [t1].[Key], [t1].[Name],  
      (CASE 
          WHEN [t3].[test] IS NULL THEN CONVERT(NVarChar(250),@p0)
          ELSE CONVERT(NVarChar(250),[t3].[Key])
       END) AS [value], 
      (CASE 
          WHEN 0 = 1 THEN CONVERT(NVarChar(250),@p1)
          ELSE CONVERT(NVarChar(250),[t4].[Key])
       END) AS [value2]
  
  FROM [dbo].[tblA] AS [t0]
  INNER JOIN [dbo].[tblB] AS [t1] ON [t0].[RefB] = [t1].[Ref]
  
  INNER JOIN [dbo].[tblD] AS [t4] ON [t0].[RefD] = ([t4].[Ref])
  
  LEFT OUTER JOIN (
      SELECT 1 AS [test], [t2].[Ref], [t2].[Key]
      FROM [dbo].[tblC] AS [t2]
      ) AS [t3] ON [t0].[RefC] = ([t3].[Ref])
  </code></pre>