sql - 为什么这个触发器改变了我的查询速度？

Question

我一直试图解决一个缓慢的触发问题，现在我已经通过反复试验，我仍然不知道最初的问题是什么。

我正在运行的查询如下:

UPDATE tblA 
SET X = NULL
WHERE X IS NOT NULL AND Z = 0

它更新了大约 30k 行。

导致问题的 AFTER INSERT, UPDATE 触发器在 tblA 上的部分是这样的:

IF EXISTS(SELECT 1
          FROM inserted
          LEFT JOIN deleted ON deleted.PK = inserted.PK
          WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
              OR inserted.Y <> deleted.Y
BEGIN

    -- The above condition is not met for my query so we would never get here
    INSERT INTO tblB
    (...)
    SELECT
    inserted.X,
    ...
    FROM
    inserted
    LEFT JOIN deleted ON deleted.PK = inserted.PK
    WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
        OR inserted.Y <> deleted.Y

END

我相信包含上面的 IF EXISTS 是为了在没有插入实际发生时阻止潜在的循环 INSERT 触发器触发，但这对于 tblB 来说实际上不是问题，因为它只有一个触发器。

所以我把它改成了这样:

INSERT INTO tblB
(...)
SELECT
inserted.X,
...
FROM
inserted
LEFT JOIN deleted ON deleted.PK = inserted.PK
WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
    OR inserted.Y <> deleted.Y

并且更新查询时间现在已经从 > 1 小时减少到大约 30 秒。

我预计它需要完全相同的时间。为什么它更快？

更新:检查使用慢触发器运行更新查询的执行计划后

IF EXISTS 检查的成本为 0%，其中 73% 的成本用于另一个触发器的语句，该语句将更改插入到审计表中。这本身似乎并不合理，因为该语句非常复杂，有很多连接，但我不知道为什么我改写 IF EXISTS 会产生任何不同。也许我的 IF EXISTS 检查以某种方式干扰了审计表插入以减慢它们的速度，但我不知道为什么新版本不做同样的事情，因为它包含相同的 SELECT。
[这笔费用的大部分用于急切的 table 线轴。]

另外 13% 的查询成本花在第三个触发器上，如果特定列值发生更改，该触发器会更新 tblA 上的时间戳。这再次加入了插入和删除，加上 tblA。此更新语句对我的查询没有影响，因为 X 列更改不值得更新时间戳。
[这个成本在 tblA 和 insert 之间的哈希匹配内部连接和聚集索引更新之间分摊 - 似乎是合理的。]

更令人困惑的是:如果我禁用了花费 73% 时间的触发器，但保留上面提到的旧触发器而不进行更改，我的查询仍然需要很多小时才能运行。我还没有尝试禁用时间戳触发器。

查看使用快速触发器时的查询计划，比率几乎完全相同，但总体时间更少。

Answer 1

请调查执行计划，看看每次运行之间有什么区别。我猜 SQL-server 为您的 exists(...) 查询使用了与 insert-select 不同的执行计划，因为它不必在第一种情况下访问所有列。如果存在令人困惑的索引或令人困惑的统计信息，优化可能会感到困惑并选择一个非常糟糕的计划。因此，在调查并保存执行计划后，尝试重新组织/重建所有索引并重新计算该表的统计信息。

问候， 罗布