主页 / dba / 问题 / 157353
Accepted
TheGameiswar
Asked: 2016-12-07 06:58:23 +0800 CST

在 IF EXISTS 中包装查询使其非常慢

  • 772

我有以下查询：

select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)

上面的查询在三秒内完成。

如果上面的查询返回任何值，我们希望存储过程退出，所以我重写如下：

If Exists(
select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
)
Begin
Raiserror('Source missing',16,1)
Return
End

然而，这需要 10 分钟。

我可以像下面这样重写上面的查询，它也可以在不到 3 秒的时间内完成：

  select databasename 
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source
if @@rowcount >0
Begin
Raiserror('Source missing',16,1)
Return
End

上面重写的问题是上面的查询是更大的存储过程的一部分，它返回多个结果集。在 C# 中，我们遍历每个结果集并进行一些处理。

上面返回一个空结果集，所以如果我采用这种方法，我必须更改我的 C# 并再次进行部署。

所以我的问题是，

为什么使用 just IF EXISTSchanges 计划要花这么多时间？

以下是可能对您有帮助的详细信息，如果您需要任何详细信息，请告诉我：

  1. 创建表和统计脚本以获得与我相同的计划
  2. 缓慢的执行计划

  3. 快速执行计划

    使用 Brentozar 的慢速计划 粘贴计划 使用 Brentozar 的
    快速计划 粘贴计划

注意：两个查询是相同的（使用参数），唯一的区别是EXISTS（虽然我在匿名时可能犯了一些错误）。

建表脚本如下：

http://pastebin.com/CgSHeqXc -- 小表统计
http://pastebin.com/GUu9KfpS -- 大表统计

sql-server

3 个回答

  1. Best Answer

    正如Paul White在他的博客文章中所解释的那样：优化器内部：深度行目标引入EXISTS了一个行目标，它更喜欢NESTED LOOPS或MERGE JOIN超过HASH MATCH

    作为最后一个例子，考虑一个逻辑半连接（例如 EXISTS 引入的子查询）共享总体主题：它应该被优化以快速找到第一个匹配行。

    在您的查询中，这显然恰好引入了嵌套循环并消除了并行性，从而导致计划变慢。

    因此，您可能需要找到一种方法来重写查询而不使用查询中的NOT EXISTSfrom。

    您可能会使用 a 重写查询LEFT OUTER JOIN并通过测试检查 smalltable 中没有一行NULL

    If EXISTS(
    SELECT databasename
    FROM somedb.dbo.bigtable l
    LEFT JOIN dbo.smalltable c ON c.source = l.source
    WHERE databasename = 'someval'
    AND source <> 'kt'
    AND c.source IS NULL
)

    您也可以使用EXCEPT查询，具体取决于您需要比较的字段数量，如下所示：

    If EXISTS(
   SELECT source
   FROM somedb.dbo.bigtable l
   WHERE databasename = 'someval'
   AND source <> 'kt'

   EXCEPT

   SELECT source
   FROM dbo.smalltable
)

    请注意，Aaron Bertrand有一篇博客文章提供了他更喜欢 NOT EXISTS 的原因，您应该通读这篇文章，看看其他方法是否更有效，并了解 NULL 值时潜在的正确性问题。

    相关问答：IF EXISTS taking longer than embedded select statement

    • 20

  2. 我遇到过同样的问题，我确实设法通过避免使用“EXISTS”并使用“COUNT()”函数和“IF...ELSE”语句来解决问题。

    对于您的示例，请尝试以下操作：

    IF
(
    SELECT
        COUNT(l.databasename) + 1 AS databasename
    FROM somedb.dbo.bigtable AS l

    WHERE   l.databasename ='someval'
        AND l.[source]  <> 'kt'
        AND NOT EXISTS(SELECT 1 FROM dbo.smalltable AS c WHERE c.[source]=l.[source])
) > 1 --Acts like EXISTS
BEGIN
    RAISERROR('Source missing', 16, 1)
RETURN
END

    我在计数中添加“+ 1”的原因是我可以在 IF 条件中使用“> 1”，使用“> 0”或“<> 0”将触发查询以使用嵌套循环而不是 HASH匹配。还没有研究为什么会发生这种情况，找出原因会很有趣。

    希望有帮助！

    • 1

  3. 您需要使用显式连接重写您的查询，并像这样指定要使用的连接操作（循环、散列或合并）。

    If not exists(
    select databasename 
    from somedb.dbo.bigtable l
    inner hash join dbo.smalltable c 
        on c.source = l.source
where databasename ='someval' and source  <>'kt')
begin
    Raiserror('Source missing',16,1)
    Return
end

    当使用 EXISTS 或 NOT EXISTS 时，SQL Server 使用 NESTED LOOP 操作生成查询计划，假设它应该逐一遍历集合中的所有行，寻找满足条件的第一行。使用 HASH JOIN 会加快速度。

    • 0

相关问题