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;