(SQL Server 2012 being used)
I found some topics on query optimization, and comparing EXISTS to COUNT, but I couldn't find this exact problem.
I have a query that looks something like this:
select * from
tblAccount as acc
join tblUser as user on acc.AccountId = user.AccountId
join tblAddress as addr on acc.AccountId = addr.AccountId
... **a few more joins**
where acc.AccountId in (
select * accountid from
(select accountid, count(*) from tblUser
where flag = 1
group by accountId) as tbl where c != 1
**Exact same query as above**
RAISERROR('Account found without exactly one flagged user.', 16, 1);
print 'test passed.'
Did you try running the original query with TOP 1? most likely it will be just as slow.
Sometimes when the optimizer thinks that something is very likely and going to return a vast set of data with little effort (i.e. almost all records are going to get returned), it chooses mostly loop joins because it only needs to get the first one and a loop join is good for only getting a couple records. When that turns out to not be true, it takes forever and a day to get results.
In your case, it sounds like it's very rare, so this choice hurts badly. Try instead doing something like
SELECT @count = COUNT(*) FROM ... and then checking if that count is non-zero.