title: Why does IF (query) take over an hour, when query takes less than a second?
body: <p>I have the following SQL:</p>
  
  <pre><code> IF EXISTS
   (
      SELECT
          1
      FROM
          SomeTable T1
      WHERE
          SomeField = 1
      AND SomeOtherField = 1
      AND NOT EXISTS(SELECT 1 FROM SomeOtherTable T2 WHERE T2.KeyField = T1.KeyField)
  )
      RAISERROR ('Blech.', 16, 1)
  </code></pre>
  
  <p>The <code>SomeTable</code> table has around 200,000 rows, and the <code>SomeOtherTable</code> table has about the same.</p>
  
  <p>If I execute the inner SQL (the <code>SELECT</code>), it executes in sub-second time, returning no rows. But, if I execute the entire script (<code>IF...RAISERROR</code>) then it takes well over an hour. <strong>Why?</strong></p>
  
  <p>Now, obviously, the execution plan is different - I can see that in Enterprise Manager - but again, <em>why</em>?</p>
  
  <p>I could probably do something like <code>SELECT @num = COUNT(*) WHERE</code> ... and then <code>IF @num &gt; 0 RAISERROR</code> but... I think that's missing the point somewhat. You can only code around a bug (and it sure looks like a bug to me) if you know that it exists. </p>
  
  <hr>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>I should mention that I already tried re-jigging the query into an OUTER JOIN as per @Bohemian's answer, but this made no difference to the execution time.</p>
  
  <hr>
  
  <p><strong>EDIT 2</strong>:</p>
  
  <p>I've attached the query plan for the inner <code>SELECT</code> statement:</p>
  
  <p><img src="https://i.stack.imgur.com/2wb2L.png" alt="Query Plan - inner SELECT statement"></p>
  
  <p>... and the query plan for the whole <code>IF...RAISERROR</code> block:</p>
  
  <p><img src="https://i.stack.imgur.com/hB1ka.png" alt="Query Plan - whole IF statement"></p>
  
  <p>Obviously these show the real table/field names, but apart from that the query is exactly as shown above.</p>