BUG: UPDATE Trigger May Have Long Parse and Compile Time (315967)

SYMPTOMS

When you modify data in a table with a trigger, it may take longer to compile in SQL Server 2000 than in Microsoft SQL Server 7.0 under the following conditions:

The trigger contains an UPDATE statement.
The UPDATE statement contains a JOIN clause.
Three (3) or more tables are listed in the JOIN clause.
Either the INSERTED or DELETED tables are listed in the JOIN clause.

The compile time varies depending on:

The number of tables involved in the JOIN.
The number of columns in each table.
The number of rows in each table.

High processor (CPU) utilization may also occur because of this problem.

WORKAROUND

To work around this behavior, use one of the following options:

Replace the trigger with a stored procedure to enforce the same UPDATE logic.

Use a TABLE variable in place of INSERTED or DELETED. For example:

DECLARE @tInserted TABLE (col1 int, col2 int)
INSERT INTO @tInserted SELECT * FROM INSERTED

Create a temporary table to use in place of INSERTED or DELETED. For example:
```
SELECT * INTO #tInserted FROM INSERTED 
					
```

MORE INFORMATION

To obtain the compile time for a statement, execute SET STATISTICS TIME ON before you run a query. The entries that follow "SQL Server parse and compile time:" contain the CPU cycles required to compile a statement and the actual time the compilation required. The sample output follows.

   SQL Server parse and compile time: 
   CPU time = 250 ms, elapsed time = 302 ms.

A performance impact only occurs when SQL Server cannot find the trigger execution plan in the procedure cache.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

243586 INF: Troubleshooting Stored Procedure Recompilation

SQL Server Books Online; topics: "Procedure Cache"; "SQL Server Memory Pool"