title: Why would wrapping a TSQL query in an if statement increase its runtime significantly?
body: <p>I'm having odd performance difference when running the same query two different ways.  Literally, the only difference is whether its wrapped in an if statement.</p>
  
  <p>This query is actually part of a larger one, but I've isolated it as the culprit.</p>
  
  <p>Running the query by itself returns almost immediately (returns 0)</p>
  
  <pre><code>select COUNT(*) from Responses r where r.ResponseID not in (
      select ResponseID from data.GamingReport_Computerized
  ))
  </code></pre>
  
  <p>Ultimately, I want to avoid running complex calculations based on the result of that query, so I wrap it in an if statement like so, but it takes much longer to run (~10 seconds) and I can't figure out why:</p>
  
  <pre><code>if (0 = (select COUNT(*) from Responses r where r.ResponseID not in (
      select ResponseID from data.GamingReport_Computerized
  )))
  begin select 'update will be skipped to save time' end
  else begin select 'missing rows will be inserted' end
  </code></pre>
  
  <p>The data set does not change so in both cases the result is zero / 'update will be skipped', and yet running these two versions of the same query always results in the first version completing quickly, and the second version taking about 10-12 seconds to complete.</p>
  
  <p>Update: here is a screenshot of the query execution plans for comparison.  Why are they so different?  Its very unexpected to me.</p>
  
  <p><img src="https://i.stack.imgur.com/FD1Hi.png" alt="Query Execution Plans">
  <img src="https://i.stack.imgur.com/it0mt.png" alt="Detailed Index Scan/Seek performance"></p>
  
  <p>Update 2: In response to a suggestion in the comments, I wanted to mention that the following query performs identically to the second version above, with the same execution plan and no increase in performance (i.e. using 'exists' instead of comparing count(*) to zero makes no difference).</p>
  
  <pre><code>if exists(select 1 from Responses r where r.ResponseID not in (
      select ResponseID from data.GamingReport_Computerized
  ))
  begin select 'missing rows will be inserted' end
  else begin select 'update will be skipped to save time' end
  </code></pre>