Inserted Scanning Performance
Introduction
We're pulling values from an "inserted" table that is created in an
insert trigger. When we do an insert and view the execution plan in
Query Analyzer, the "Inserted Scan" accounts for 98% of the entire
query cost
insert trigger. When we do an insert and view the execution plan in
Query Analyzer, the "Inserted Scan" accounts for 98% of the entire
query cost
So, in this article we are trying to a closer look related to the Inserted and Deleted table performance and how to optimize the performance of Inserted and Deleted table.
Please note that the details related to Inserted and Deleted table and how to use it in Trigger is the out of scope of this article.
Point in Focus
Facts related to Inserted and Deleted Tables
Why the Performance is going down
How to Improve the Performance
Facts related to Inserted and Deleted Tables
Why the Performance is going down
How to Improve the Performance
Facts related to Inserted and Deleted Tables
In MS SQL Server 2000, these logical tables internally refer to database transaction log to provide data when user queries them.
In SQL Server 2005, these logical tables are maintained in tempdb and they are maintained using the new technology Row versioning.
Accessing of logical tables is much faster in MS SQL Server 2005 when compared to MS SQL Server 2000 as the load is removed from transaction log and moved to tempdb.
We cannot create any Index in the Logical table.
Why the Performance is going down
In general cases the performance is going too degraded if we use the Inserted and Deleted table more than once within triggers.
How to Improve the Performance
Performance can be improved, if we putting it into temp database by using temp table and index it well.
If anyone has any suggestion related to improving the performance of Inserted and deleted table, please make comments on this post.
