Performance in Triggers
-
Hi,
I am working on a little performance improvement for my application.We are using SQL Server 2000 as our database.
The logic is that we have a trigger on a table that adds a record in the history table for every insert/update on the main table.
Following is the code of the trigger:-
IF exists (SELECT * FROM inserted)
BEGIN
IF exists (SELECT * FROM deleted)
--IF a deleted record also exists, this is an Update
BEGIN
SET @Action='U'
END
ELSE
--Otherwise this is an Insert
BEGIN
SET @Action='I'
END
Insert into Historytable(columns, [ACTION])
SELECT columnsFROM Inserted
END
The problem is when i see the query execution plan, the trigger is taking most of the time with the following statements taking almost 90% of the time:- 1) Select * from inserted. 2) Select * from deleted.
3) Select columns from inserted.
Any suggestion how I can improve the performance, so that the trigger does not take so long.
Jaya
-
Assuming that you have various methods of maintaining your data and can't put the logic in stored procedures try the following
INSERT INTO dbo.HistoryTable (column1, column2......column99999,Action)
SELECT I.column1, I.column2, I.column3 ,....column99999 ,
CASE D.PrimaryKey WHEN NULL THEN 'I' ELSE 'U' END AS Action
FROM inserted AS I
LEFT JOIN deleted AS D
ON I.PrimaryKey = D.Primary Key
-
David ..Thanks for your response.I tried using the approach suggested by you. But the performance didn't seem to improve much. The single query took all the time used by the prev 3 queries.
Browsing thru more responses here, I found that another user Gary Milton had faced similar problem. I tried using his suggestion and the query worked wonders.
His suggestion was to create a temporary table for the inserted table and the deleted table as in
SELECT * INTO #Inserted FROM Inserted
SELECT * INTO #Deleted FROM Deleted
Still unsure how creating the temp tables increased the performance so much. But for now I am happy as performance has improved.
-
Jaya,
Triggers are usually very cpu intensive for the server, using the temp table is a much better solution for this, but keep on eye on your tempdb as the amount of your data grows, make sure you have the space for the growth in your tempdb or it could lock up if you run out of space.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply