为什么此触发器更改会加快我的查询速度？

Question

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

我正在运行的查询如下：

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

它更新了大约 30k 行。

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

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。 [大部分成本都花在了急切表假脱机上。]

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

增加更多混乱：如果我禁用花费 73% 时间的触发器，但保留上面提到的旧触发器如果没有我的更改，我的查询仍然需要很多小时才能运行。我还没有尝试禁用时间戳触发器。

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

Answer 1

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

问候，罗布

Answer 2

好吧，我不太确定两者之间发生了什么，但我可以为您提供一些技巧来加快速度，

我要更改的第一件事是：

WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL) 

对此：

WHERE (inserted.Y >'' AND deleted.Y IS NULL) 

IS NULL 会导致索引查找，其中 > ;'' 允许 sql 进行查找并给出相同的结果集（取决于 y 是否为 int，如果它是 varchar 那么您可能会更改为 >=''）