SQL Server:如果EXISTS大大降低了查询速度 [英] SQL Server: IF EXISTS massively slowing down a query

问题描述

(正在使用SQL Server 2012)

(SQL Server 2012 being used)

我找到了一些有关查询优化的主题，并将EXISTS与COUNT进行了比较，但是我找不到这个确切的问题.

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

此查询可立即运行(尽管数据库很大，大约70Gb).

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

当我将查询包装在EXISTS中时，如下所示:

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

突然，查询大约需要5-6秒才能完成.我试过指定IF EXISTS(选择TOP 1 FROM ...)，还尝试了NOT EXISTS(这甚至更慢).但是都没有加快速度.

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.

如果正常的选择查询基本上立即完成，那么有人知道为什么将其包装在EXISTS中会导致大量额外的计算吗?和/或任何人都有解决此问题的想法(如果原始查询根本找不到任何记录，我只是想抛出一个错误).

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).

谢谢！

推荐答案

您尝试使用TOP 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.

在您的情况下，这听起来非常罕见，因此这种选择会带来极大的伤害.尝试做类似SELECT @count = COUNT(*) FROM ...的操作，然后检查该计数是否为非零.

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.

这篇关于SQL Server:如果EXISTS大大降低了查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！