SQL Trigger performance

Question

I have a trigger on a table, it's basically this

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

It runs extremely slow when it does the insert (20 seconds). The deletes are fast. Playing around I tried doing this instead:

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

By using a table variable it now runs instantly (under 1 second).

I'm not sure why though. Is there any reason why changing to a table variable would make such a difference?

Answer 1

Not sure why you'd need the WHERE clause at all for the INSERT operation.

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