Link to home
Create AccountLog in
Avatar of AEPRTD
AEPRTD

asked on

Is Old SQL Server Table Trigger Performance Issue Workaround Still Valid?

I am looking for on-line documentation or moral support!

Years ago, we had a developer that determined we had this problem with our SQL Server 2000 installation:   BUG: UPDATE Trigger May Have Long Parse and Compile Time

So he added the workaround code (line 4 and line 6):
CREATE TRIGGER [dbo].[ti_MtsEHistory] ON [dbo].[MtsEHistory] FOR INSERT AS
BEGIN

DECLARE @InsertedBugWorkAroundtable(EventID int)

	INSERT INTO @InsertedBugWorkAround SELECT EventID FROM inserted 

	-- Set the Created field for any rows being inserted.
	 UPDATE MtsEHistory
	    SET Created = GETDATE(),
		ModifyDateTime = GETDATE()
	   FROM MtsEHistory, inserted
	  WHERE MtsEHistory.EventID = inserted.EventID

END

Open in new window


When I joined the company we were still on SQL Server 2000.  I ran tests by removing the workaround code and could not recreate the performance problem.  

We are now on SQL Server 2008.  Regardless of whether the UPDATE statement in the code above needs refactoring, I need help proving to my supervisor that the workaround is no longer needed.  (He won't take my word for it.)  The MtsEHistory table has millions of row, lots of columns and is one of our most used tables.  It is common sense that any trigger code that doesn't do anything useful should be removed.
Avatar of QuinnDex
QuinnDex

first thing to do is find any other queries Sp's index's etc that reference the InsertedBugWorkAroundtable

removing the code or the whole trigger will only stop that table being updated any tests you did will still be using the table during execution and if its fundamental to the query will not be using new data
ASKER CERTIFIED SOLUTION
Avatar of John_Vidmar
John_Vidmar
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AEPRTD

ASKER

I did not realize all four conditions had to exist for the bug to exist.  And as you stated, it wasn't just the act of declaring and populating a table variable that fixed the problem, it was the subsequent use of the table variable in joins that fixed the problem.