SQL Server: IF EXISTS massively slowing down a query

Question

(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

This query runs in an instant (although the db is quite big, around 70Gb).

When I wrap the query in an EXISTS as in:

if exists
(
  **Exact same query as above**
)
begin
RAISERROR('Account found without exactly one flagged user.', 16, 1);
end
else
begin
  print 'test passed.'
end

Suddenly the query takes about 5-6 seconds to complete. I've tried specifying IF EXISTS (SELECT TOP 1 FROM... and also tried NOT EXISTS (which was even slower). But neither work to speed this up.

If the normal select query completes basically instantly, then does anyone know why wrapping it in the EXISTS causes so much extra computation? And/or anyone have any ideas to work around this (I'm just trying to throw an error if any records are found at all by the original query).

Thanks!

Answer 1

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.

Answer 2

Try:

if exists
(
  select 1 from... etc
)

Answer 3

Try SELECT 1 instead of top 1 *. You don't actually need to return data, you're just checking for the existence of a record.

You could also try clearing the query cache if it's a stored procedure, like this:

To get the plan handle:

SELECT qs.plan_handle
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
and p.name like '%SprocName%'
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

Then clear the cache by putting the handle into this call:

DBCC FREEPROCCACHE (0x05000F00C616D37C40E15E64010000000000000000000000);

Answer 4

You've got, what, three nested subqueries? Subqueries are always slow. Can you convert at least one of them to a join? As in:

select acc.AccountId from tblAccount as acc
    join tblUser as user on acc.AccountId = user.AccountId
    join tblAddress as addr on acc.AccountId = addr.AccountId
    join (select accountid, count(*) as c from tblUser
          where flag = 1
          group by accountId) as tbl ON tbl.accountid = user.accountid
    where tbl.c != 1

Answer 5

What it works for me was to set the result of the query into a variable, then compare the variable, dont ask me why, it just woked for me. Cannot explain it

Answer 6

I've fought this issue as well.

The query was 10ms when I ran it on its own but once I put it in the If Exists it went to 4 minutes. No matter what I tried it didn't go back to 10ms. The issue was re-produced on 4 different servers but not on 2 servers. Servers all had the same db backup and same patch level of mssql 2012. servers were on different OS and varying hardware settings.

I tried

• adjusting the max memory grant - no affect
• changing the threshold for parallelism - no affect
• rewrite the query to make it simpler - no affect
• use top 1 - no affect
• cleared the cache between changes - no affect
• break the query into some indexed views where I could (can't do it to parts using outer join) - no affect
• applied recommended missing index - reduced time from 4 to 3 minutes but still not the 10 ms I expected.
• change the outer join to a where not in (sub-query) - no affect
• run sp_updateStats - no affect

The only solution that worked for me was to put the results in a temp table and do the if exists against that temp table.

SELECT top 1 1 AS junk INTO #me FROM yourCraxyQueryHere IF EXISTS ( SELECT 1 FROM #me ) SELECT GETDATE()

hope this helps