Why does a query inside a IF EXISTS statement not perform the same as outside
The query outside the if exists, returns in 1 seconds.
The query inside the if exists, takes 1.5 minutes to complete.
Can you help me understand what is the possible issue and how I might correct this issue?
In your specific case, this backfires. Expecting to find a match fast, the execution plan uses Nested Loops joins and other constructions that are better for small sets of data but scale bad. But the expectation was wrong, there was no match at all so the Nested Loops join ends up processing ALL rows.
The original plan uses Hash Match for the same join, which has a higher startup cost but scales much better.
In this specific case (and really in this specific case only!!) you might get better performance by replacing IF EXISTS with what I normally consider an anti-pattern: "IF (SELECT COUNT() FROM …) > 0" (In this case, COUNT() would replace DISTINCT gc.Contract_Code in your query.
Again, I cnanot stress this enough: in 99.9% of all cases I would do the reverse, I have already improved performance of countless procedure by replacing the COUNT() > 0 check with an EXISTS check. This is really a weird exception. When you make this change, I suggest adding a huge comment block to explain the reason. And create a recurring task for yourself to re-check the performance of the two alternatives every two or three months or so. When your data changes, you may revert to a more typical pattern and then the COUNT() will suddenly start to hurt instead of help you.