sql-server-2008 - SQL 触发器性能

Question

我在一个表上有一个触发器，基本上就是这个

ALTER TRIGGER xx
FOR UPDATE,DELETE,INSERT
AS
   DELETE FROM other WHERE id in (SELECT id from deleted)
   DELETE FROM other WHERE id in (SELECT id from inserted)
   INSERT INTO other() VALUES() WHERE id in (SELECT id from inserted)

GO

插入时它运行得非常慢(20 秒)。删除速度很快。玩弄我尝试这样做:

ALTER TRIGER xx
FOR UPDATE,DELETE,INSERT
AS
   DECLARE @tinserted TABLE ( id int) 
   INSERT INTO @tinserted select id from inserted;
   DELETE FROM other WHERE id in (SELECT id from deleted)
   DELETE FROM other WHERE id in (SELECT id from inserted)
   INSERT INTO other() VALUES() WHERE id in (SELECT id from @tinserted)

GO

通过使用表变量，它现在可以立即运行(不到 1 秒)。

虽然我不确定为什么。更改为表变量会有如此大的不同吗？

Answer 1

不确定为什么 INSERT 操作需要 WHERE 子句。

INSERT INTO other(column1, column2, ...)
    SELECT column1, column2, ...
        FROM inserted;