Why does IF (query) take over an hour, when query takes less than a second?

The IF does not magically turn off optimizations or damage the plan. The optimizer just noticed that EXISTS only needs one row at most (like a TOP 1). This is called a "row goal" and it normally happens when you do paging. But also with EXISTS, IN, NOT IN and such things.

My guess: if you write TOP 1 to the original query you get the same (bad) plan.

The optimizer tries to be smart here and only produce the first row using much cheaper operations. Unfortunately, it misestimates cardinality. It guesses that the query will produce lots of rows although in reality it produces none. If it estimated correctly you'd just get a more efficient plan, or it would not do the transformation at all.

I suggest the following steps:

1. fix the plan by reviewing indexes and statistics
2. if this didn't help, change the query to IF (SELECT COUNT(*) FROM ...) > 0 which will give the original plan because the optimizer does not have a row goal.

It's probably because the optimizer can figure out how to turn your query into a more efficient query, but somehow the IF prevents that. Only an EXPLAIN will tell you why the query is taking so long, but I can tell you how to make this whole thing more efficient... Indtead of using a correlated subquery, which is incredibly inefficient - you get "n" subqueries run for "n" rows in the main table - use a JOIN.

Try this:

IF EXISTS (  SELECT 1  FROM SomeTable T1  LEFT JOIN SomeOtherTable T2 ON T2.KeyField = T1.KeyField  WHERE SomeField = 1  AND SomeOtherField = 1  AND T2.KeyField IS NULL) RAISERROR ('Blech.', 16, 1)

The "trick" here is to use s LEFT JOIN and filter out all joined rows by testing for a null in the WHERE clause, which is executed after the join is made.

Please try SELECT TOP 1 KeyField. Using primary key will work faster in my guess.

NOTE: I posted this as answer as I couldn't comment.