SQL Server：IF EXISTS 严重拖慢查询速度

Question

SQL Server：IF EXISTS 严重拖慢查询速度

sqlsql-serversql-server-2012query-optimizationexists

9

（使用SQL Server 2012）

我找到了一些关于查询优化和比较EXISTS和COUNT的主题，但我找不到这个确切的问题。

我有一个类似于这样的查询：

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）。

当我用EXISTS包裹查询时，如下所示：

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 (SELECT TOP 1 FROM...)，也尝试了NOT EXISTS（甚至更慢）。但是，这两种方法都不能加速查询。

如果正常的选择查询基本瞬间完成，那么有人知道为什么将其包装在EXISTS中会导致如此多的额外计算吗？或者有任何想法可以解决这个问题（我只是想通过原始查询抛出一个错误，如果发现任何记录）。

谢谢！

- John Darvill

这是完全相同的查询包含在EXISTS中吗？选择top 1不会有任何作用，因为exists扫描表格，如果找到记录，则返回true，并且不关心其他计算。但我首先会从执行计划开始思考。 - Evaldas Buinauskas

是的，完全相同的查询。已经编辑了原始问题以使其更清晰。不确定您所说的执行计划是什么意思？ - John Darvill

也许普通查询被缓存了，而不是每次都实际查询数据，与exist()相反。 - JamieD77

执行计划显示查询执行期间实际完成的内容。您可以按照链接中所示启用它，然后截取屏幕并上传两个计划，以便我们查看发生了什么。http://i.imgur.com/oiLRdLb.png - Evaldas Buinauskas

@user1221684 - SQL Server不会缓存实际的查询结果，只会在计划缓存中缓存已编译的执行计划和在缓冲池中的基础数据/索引页。 - Martin Smith

6个回答

3

我也曾遇到过这个问题。

当我单独运行查询时，查询的响应时间为10毫秒，但一旦将其放入 "If Exists" 中，响应时间就变成了4分钟。我尝试了各种方法，但都没有将响应时间降回到10毫秒。这个问题在4个不同的服务器上都出现过，但在其中2个服务器上未发生。这些服务器都使用相同的数据库备份和同样的mssql 2012补丁级别，但运行不同的操作系统和硬件设置。

我尝试了以下方法：

- 调整最大内存授权 - 没有影响 - 更改并行度阈值 - 没有影响 - 重写查询以使其更简单 - 没有影响 - 使用 top 1 - 没有影响 - 在更改之间清除缓存 - 没有影响 - 将查询拆分为某些索引视图（可以对使用外部连接的部分执行此操作） - 没有影响 - 应用建议的缺失索引 - 将时间从4分钟缩短到3分钟，但仍然无法达到我预期的10毫秒。 - 将外部连接更改为 not in (子查询) - 没有影响 - 运行sp_updateStats - 没有影响

唯一有效的解决方法是将结果放入临时表中，然后对该临时表进行if exists检查。

希望这可以帮到你。

- Jack B

如何示例调整最大内存授予，更改并行度阈值，清除更改之间的缓存，将查询分解为一些索引视图，应用建议的缺失索引和运行sp_updateStats？ - Kiquenet

我曾经遇到过类似的情况，当查询被包含在IF EXISTS中时，查询时间从大约5秒变成了12分钟。我选择将其选入一个临时表中，然后在临时表上执行IF EXISTS，现在查询时间又回到了5秒。 - Robert Sievers

0

尝试：

if exists
(
  select 1 from... etc
)

- Cee

3

由于IF EXISTS仅检查存在性并不实际返回任何数据，因此这是毫无意义的，并且与IF EXISTS(SELECT *....)完全相同。 - marc_s

“*” 将被扩展为一些可能很大的列列表，然后确定 EXISTS 的语义不需要这些列中的任何一个，因此基本上所有这些列都可以被删除。在查询编译期间，“SELECT 1” 将避免检查该表的任何不必要的元数据。 - Cee

@marc_s - 它会检查列元数据，但同样也会发生在 1 上。 - Martin Smith

2

@MartinSmith：好的，但重点是IF EXISTS (SELECT * ...)和IF EXISTS (SELECT 1 ....)是相同的，至少在SQL Server中产生完全相同的执行计划 - 因此使用SELECT 1而不是SELECT *并没有任何收益 - 对吗？ - marc_s

1

@marc_s - 是的。在我的答案中，我们研究了1与*以及扩展列元数据的影响，链接在这里https://dev59.com/VXI-5IYBdhLWcg3w9tdw#6140367。`SELECT 1 WHERE EXISTS (SELECT 1 FROM T);仍然可能因为缺少对T表的列权限而意外失败，这表明权限是需要检查的。无论是使用1还是*`，相对于列数较慢的编译都会发生。 - Martin Smith

@marc_s 使用select 1 from...而不是select * from...的一个优点是它传达了意图，即我们不打算使用任何这些列。是的，因为它被包含在exists检查中，所以意图在某种程度上是明显的，但我认为select 1仍然增加了可读性。 - Geoff

0

我的解决办法是将查询结果存储到变量中，然后比较这个变量。不要问我为什么，反正对我有效。无法解释。

- Cristian G. Acosta

编辑您的答案以包含所描述的SQL脚本将会很有帮助。您可以根据需要编辑或重命名敏感的元数据值。 - JohnH

-1

你有三个嵌套子查询？子查询总是很慢的。你能把它们中的至少一个转换成连接吗？就像这样：

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

- Mike K

-2

尝试使用SELECT 1而不是top 1 *。实际上，您不需要返回数据，只需检查记录是否存在。

如果它是存储过程，您还可以尝试清除查询缓存，如下所示：

获取计划句柄的方法：

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

然后通过将句柄放入此调用中清除缓存：

DBCC FREEPROCCACHE (0x05000F00C616D37C40E15E64010000000000000000000000);

- JonathanS

2

在其他答案中的评论中提到，将 * 更改为 1 不会有任何区别。 - Martin Smith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Tom · Accepted Answer

你是否尝试使用 TOP 1 运行原始查询？很可能它同样会很慢。

有时，当优化器认为某个查询非常可能返回大量数据且几乎不费吹灰之力（即将返回的记录几乎是所有记录），它会选择使用大多数的循环连接，因为它只需要获取第一条记录，并且循环连接适合仅获取少量记录。但当这种情况并不成立时，获取结果需要很长时间。

在你的情况下，这种情况似乎很少发生，因此这种选择会带来很大的影响。相反，尝试执行类似于 SELECT @count = COUNT(*) FROM ... 然后检查该计数是否非零。