title: Performance - Select query with left join and null check
body: <p>I have two different tables which are called as <code>Processing (30M records for now)</code> and <code>EtlRecord (4.3M records for now)</code>.
  As the name of tables suggest, these tables will be used for normalization of data with ETL.</p>
  
  <p>We are trying to process records with batches where we have 1000 records in each batch.</p>
  
  <pre><code>SELECT TOP 1000 P.StreamGuid
  FROM [staging].[Processing] P           (NOLOCK)
  LEFT JOIN [core].[EtlRecord] E          (NOLOCK)    ON E.StreamGuid = P.StreamGuid
  WHERE E.StreamGuid IS NULL
  AND P.CompleteDate IS NOT NULL
  AND P.StreamGuid IS NOT NULL
  </code></pre>
  
  <p>Execution of this query takes around 20 seconds now. And we are expecting to have more and more data especially in EtlRecord table. To be able to improve the performance of this query I check the actual execution plan which I shared below.</p>
  
  <p><a href="https://i.stack.imgur.com/NIELG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NIELG.png" alt="Execution plan"></a></p>
  
  <p>As you can see, the most time consuming part is index seek to determine null records in EtlRecord table. I have tried several changes but couldn't able to improve it.</p>
  
  <p><strong>Additional notes</strong></p>
  
  <ul>
  <li>All suggested indices by execution plan already applied to tables. So there is no further index suggestion.</li>
  <li>There are 8 columns in <code>Processing</code> table which are mostly boolean flags and 4 columns in <code>EtlRecord</code> table.</li>
  <li>EtlRecord table is only used by single procedure. So there is no issue with transaction lock.</li>
  </ul>
  
  <p>Any suggestions to improve this query will be really helpful.</p>