Microsoft KB Archive/315967

= BUG: UPDATE Trigger May Have Long Parse and Compile Time =

Article ID: 315967

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q315967



BUG #: 356425 (SHILOH_BUGS)



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 



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000.



MORE INFORMATION
To obtain the compile time for a statement, execute SET STATISTICS TIME ON before you run a query. The entries that follow &quot;SQL Server parse and compile time:&quot; 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.