插入/删除触发器表上的慢速联接 [英] Slow join on Inserted/Deleted trigger tables

问题描述

我们有一个触发器，该触发器为一个表创建审计记录，并将插入的和删除的表联接起来，看是否任何列均已更改。该连接对于小型数据集一直运行良好，但是现在我要更新约100万行，而且几天之内无法完成。我尝试更新选定数量的不同数量级的行，这显然是指数的，如果插入 / 删除表正在被扫描以进行联接。

We have a trigger that creates audit records for a table and joins the inserted and deleted tables to see if any columns have changed. The join has been working well for small sets, but now I'm updating about 1 million rows and it doesn't finish in days. I tried updating a select number of rows with different orders of magnitude and it's obvious this is exponential, which would make sense if the inserted/deleted tables are being scanned to do the join.

我尝试创建索引，但收到错误：
无法找到对象插入，因为它不存在或您没有权限。

I tried creating an index but get the error: Cannot find the object "inserted" because it does not exist or you do not have permissions.

有什么方法可以使其更快？

Is there any way to make this any faster?

推荐答案

插入连接列上索引的临时表可以很好地改善插入 和 。

Inserting into temporary tables indexed on the joining columns could well improve things as inserted and deleted are not indexed.

您可以检查 @@ ROWCOUNT 在触发器内，因此您只能在某个阈值行数以上执行此逻辑，尽管在SQL Server 2008上，如果由于 MERGE 语句（它将返回所有受影响的行的总数 MERGE 动作不仅限于与该特定触发器相关的动作。

You can check @@ROWCOUNT inside the trigger so you only perform this logic above some threshold number of rows though on SQL Server 2008 this might overstate the number somewhat if the trigger was fired as the result of a MERGE statement (It will return the total number of rows affected by all MERGE actions not just the one relevant to that specific trigger).

在这种情况下，您可以执行类似 SELECT @NumRows = COUNT（*）FROM（选择前10 * FROM INSERTED）T 以查看是否满足阈值。

In that case you can just do something like SELECT @NumRows = COUNT(*) FROM (SELECT TOP 10 * FROM INSERTED) T to see if the threshold is met.

添加

您可以尝试的另一种可能性就是绕过这些较大更新的触发器。您可以使用 SET CONTEXT_INFO 设置标志并在触发器内检查其值。然后，您可以使用 OUTPUT插入。*，删除。* 来获取行的之前和之后值，而无需 JOIN 。

One other possibility you could experiment with is simply bypassing the trigger for these large updates. You could use SET CONTEXT_INFO to set a flag and check the value of this inside the trigger. You could then use OUTPUT inserted.*, deleted.* to get the "before" and "after" values for a row without needing to JOIN at all.

DECLARE @TriggerFlag varbinary(128)
SET @TriggerFlag = CAST('Disabled' AS varbinary(128)) 

SET CONTEXT_INFO @TriggerFlag

UPDATE YourTable
SET Bar = 'X'
OUTPUT inserted.*, deleted.* INTO @T

/*Reset the flag*/
SET CONTEXT_INFO 0x

这篇关于插入/删除触发器表上的慢速联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！