主页 / dba / 问题 / 15730
Accepted
db2
Asked: 2012-03-29 09:49:07 +0800 CST

在触发器中加入 INSERTED 和 DELETED 表的性能很差

  • 772

我在表上有一个 UPDATE 触发器，它监视特定列从一个特定值更改为任何其他值。发生这种情况时，它会通过单个 UPDATE 语句更新另一个表中的一些相关数据。

触发器所做的第一件事是检查是否有任何更新的行使该列的值与所讨论的值不同。它只是将 INSERTED 连接到 DELETED 并比较该列中的值。如果没有任何条件，它会提前退出，因此 UPDATE 语句不会运行。

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

在这种情况下，CUSTNMBR 是基础表的主键。如果我对此表进行大量更新（例如，超过 5000 行），即使我没有触及 CUSTCLAS 列，此语句也会占用 AGES。我可以在 Profiler 中看到它在这个语句上停滞几分钟。

执行计划很奇怪。它显示了具有 3,714 次执行和约 1850 万输出行的插入扫描。这通过 CUSTCLAS 列上的过滤器运行。它将此（通过嵌套循环）连接到已删除扫描（也在 CUSTCLAS 上过滤），该扫描仅执行一次并具有 5000 个输出行。

我在这里做了什么愚蠢的事情来造成这种情况？请注意，触发器绝对必须正确处理多行更新。

编辑：

我也试过这样写（以防 EXISTS 做了一些不愉快的事情），但它仍然很糟糕。

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN
sql-server sql-server-2008

5 个回答

  1. Best Answer

    INNER MERGE JOIN您可以使用显式或提示进行评估INNER HASH JOIN，但假设您稍后在触发器中再次使用这些表，您最好将表的内容插入索引表inserted并完成它。deleted#temp

    它们不会自动为它们创建有用的索引。

    • 11

  2. 我知道这个问题已经得到解答，但它只是最近才出现，我也遇到了这个问题，对于有数百万行的表。虽然不打折已接受的答案，但我至少可以补充一点，我的经验表明，在进行类似测试（查看一列或多列是否实际更改了其值）时，触发器性能的一个关键因素是列是否被测试实际上是UPDATE声明的一部分。我发现比较实际上不属于语句的表inserted和表之间的列会对性能造成巨大的拖累，如果这些字段是deletedUPDATEUPDATE声明（不管它们的值实际上被改变了）。如果您可以从逻辑上排除任何这些列被更改的可能性，那么为什么所有这些工作（即比较 X 行中的 N 个字段的查询）以确定是否有任何更改，如果它们不存在，这显然是不可能的在语句的SET子句中UPDATE。

    我采用的解决方案是使用仅在触发器内部工作的UPDATE()函数。此内置函数告诉您是否在UPDATE语句中指定了列，如果您关注的列不属于UPDATE. 这可以与 a 结合使用SELECT来确定这些列（假设它们存在于 中UPDATE）是否具有实际更改。我在几个审计触发器的顶部有代码，如下所示：

    -- exit on updates that do not update the only 3 columns we ETL
IF (
     EXISTS(SELECT 1 FROM DELETED) -- this is an UPDATE (Trigger is AFTER INSERT, UPDATE)
     AND (
            NOT (UPDATE(Column3) OR UPDATE(Column7)
                 OR UPDATE(Column11)) -- the columns we care about are not being updated
            OR NOT EXISTS(
                        SELECT 1
                        FROM INSERTED ins
                        INNER JOIN DELETED del
                                ON del.KeyField1 = ins.KeyField1
                                AND del.KeyField2 = ins.KeyField2
                        WHERE ins.Column3 <> del.Column3
                                 COLLATE Latin1_General_100_CS_AS -- case-sensitive compare
                        OR    ISNULL(ins.Column7, -99) <> 
                                 ISNULL(del.Column7, -99) -- NULLable INT field
                        OR    ins.[Column11] <> del.[Column11] -- NOT NULL INT field
                      )
          )
    )
BEGIN
    RETURN;
END;

    如果出现以下情况，此逻辑将继续执行触发器的其余部分：

    1. 该操作是一个INSERT
    2. 至少有一个相关字段位于SETan 的子句中， UPDATE 并且一行中的这些列中至少有一个已更改

    可能看起来很奇怪或倒退，NOT (UPDATE...) OR NOT EXISTS()但它旨在避免在没有相关列属于. SELECTinserteddeletedUPDATE

    根据您的需要，COLUMNS_UPDATED()函数是确定哪些列是UPDATE语句的一部分的另一个选项。

    • 10

  3. 我可能会尝试使用 if exists 重写

    IF EXISTS (SELECT TOP 1 i.CUSTNMBR     
            FROM INSERTED i         
            INNER JOIN DELETED d             
            ON i.CUSTNMBR = d.CUSTNMBR and d.custclass = 'Misc'  
            WHERE d.CUSTCLAS <>i.CUSTCLAS)    
BEGIN

--do your triggerstuff here
END
    • 2

  4. http://dave.brittens.org/blog/writing-well-behaved-triggers.html

    根据 Dave 的说法，您应该使用带有索引的临时表或表变量，因为虚拟 INSERTED/DELETED 表没有。如果您有可能使用递归触发器，那么您应该使用表变量来避免名称冲突。

    希望有人觉得这很有帮助，因为原来的帖子是很久以前的......

    • 1

  5. 以下代码可能会提高此触发器的性能。我不知道[custclass]列的正确数据类型，因此您需要对其进行调整。

    DECLARE @i AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
DECLARE @d AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
INSERT INTO @i SELECT CUSTNMBR, custclass FROM inserted
INSERT INTO @d SELECT CUSTNMBR, custclass FROM deleted
IF NOT EXISTS
  (SELECT * FROM @i AS i INNER JOIN @d AS d ON d.CUSTNMBR = i.CUSTNMBR
   WHERE i.custclass <> d.custclass) RETURN

    请注意，如果您的触发器代码中需要它们，您可以在这些插入和删除表的内存副本中包含其他列。当一次更新多行时，这些表上的主键将大大提高连接性能。祝你好运！

    • -1

相关问题