Inserted Scanning Performance

Introduction

Here there is an interesting case scenario mentioned bellow.

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

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

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.

Hope you like it.

Posted by: MR. JOYDEEP DAS