title: Why LEFT JOIN increase query time so much?
body: <p>I'm using SQL Server 2012 and encountered strange problem. </p>
  
  <p>This is the original query I've been using:</p>
  
  <pre><code>DELETE FROM [TABLE_TEMP]
  
  INSERT INTO [TABLE_TEMP]
     SELECT H.*, NULL 
     FROM [TABLE_Accounts_History] H
     INNER JOIN [TABLE_For_Filtering] A ON H.[RSIN] = A.[RSIN]
     WHERE
        H.[NUM] = (SELECT TOP 1 [NUM] FROM [TABLE_Accounts_History]
                   WHERE [RSIN] = H.[RSIN] 
                     AND [AccountSys] = H.[AccountSys]
                     AND [Cl_Acc_Typ] = H.[Cl_Acc_Typ]
                     AND [DATE_DEAL] &lt; @dte
                   ORDER BY [DATE_DEAL] DESC)
        AND H.[TYPE_DEAL] &lt;&gt; 'D'
  </code></pre>
  
  <p>Table <code>TABLE_Accounts_History</code> consists of <strong>3 200 000</strong> records.</p>
  
  <p>Table <code>TABLE_For_Filtering</code> is around <strong>1 500</strong> records.</p>
  
  <p>Insert took me <strong>2m 40s</strong> and inserted <strong>1 600 000</strong> records for further work.</p>
  
  <p>But then I decided to attach a column from pretty small table <code>TABLE_Additional</code> (only around <strong>100</strong> recs):</p>
  
  <pre><code>DELETE FROM [TABLE_TEMP]
  
  INSERT INTO [TABLE_TEMP]
     SELECT H.*, P.[prof_type]    
     FROM [TABLE_Accounts_History] H
     INNER JOIN [TABLE_For_Filtering] A ON H.[RSIN] = A.[RSIN]
     LEFT JOIN [TABLE_Additional] P ON H.[ACCOUNTSYS] = P.[AccountSys]
     WHERE        H.[NUM] = ( SELECT TOP 1    [NUM]
                          FROM            [TABLE_Accounts_History]
                          WHERE           [RSIN] = H.[RSIN]
                                      AND [AccountSys] = H.[AccountSys]
                                      AND [Cl_Acc_Typ] = H.[Cl_Acc_Typ]
                                      AND [DATE_DEAL] &lt; @dte
                          ORDER BY        [DATE_DEAL] DESC)
          AND H.[TYPE_DEAL] &lt;&gt; 'D' 
  </code></pre>
  
  <p>And now it takes ages this query to complete. Why is it so? How such small left join possibly can dump performance? How can I improve it?</p>
  
  <p><strong>An update:</strong> no luck so far with <code>LEFT JOIN</code>. Indexes, no indexes, hinted indexes.. For now I've found a workaround by using my first query and UPDATE after it:</p>
  
  <pre><code>UPDATE  [TABLE_TEMP]
  SET     [PROF_TYPE] = P1.[prof_type]
  FROM    [TABLE_TEMP] A1
  LEFT JOIN
          [TABLE_Additional] P1
      ON  A1.[ACCOUNTSYS] = P1.[AccountSys]
  </code></pre>
  
  <p>Takes only 5s and does pretty much the same I've been trying to achieve. Still SQL Server performance is mystery to me.</p>