Optimisation Issue: Copying trigger INSERTED or DELETED rows into table variable

I've been having performance problems with an SQL trigger. The problem relates to performing an SQL query when using a select statement with the INSERTED trigger table.

SELECT x FROM y WHERE x IN ( SELECT x FROM INSERTED )

The query execution plan points to the ( SELECT x FROM INSERTED ) taking much longer than expected. We are talking x1000s of rows here.

If I create a table variable, and I select the rows from INSERTED into the table variable, then use the table variable within the 'IN' clause it is MUCH faster.

My question is why is this faster? I've already got an idea what it might be. I've read that the INSERTED table is a virtual table and th开发者_Go百科is comes from the transaction logs and so takes substantially longer to read than using the table variable. However, I would have thought selecting the rows from the INSERTED table into the table variable would negate the positive effects of using the table variable within the 'IN' clause.

Any ideas why this is much faster?

Couldn't you just try a regular JOIN with the INSERTED table? Those often perform significantly better than those subqueries:

SELECT x 
FROM y 
INNER JOIN INSERTED i ON y.x = i.x

Not knowing all your details, it's impossible to know for sure whether this will help at all - but it's defintely worth a try!