Poor Trigger Performance - Why?
-
Not sure if anyone can help but we appear to have a serious performance problem with triggers. I have been doing some experimentation and the following examples explain my problem. I have used the query execution plan to ensure that efficient indexes are used on all tables accessed but the performance is still poor.
The main culprit is an UPDATE trigger on a table called tblPoliPolicy. If I run...
UPDATE tblPoliPolicy SET PoliStatus = 'D' WHERE PoliID = 59165
...the following code in the trigger causes the update to take around 400ms when updating 1 row of data...
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = (
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID)
WHERE PoliID IN (SELECT PoliID FROM Inserted)
I have elected to use an IN instead of a join to the ‘Inserted’ table as doing so takes even longer - around 650ms! Re-coding to use a temporary table instead of directly referencing the 'Inserted' table reduces the update to around 220ms...
SELECT *
INTO #Inserted
FROM Inserted
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = (
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID)
WHERE PoliID IN (SELECT PoliID FROM #Inserted)
Why is it quicker to use my own temporary table rather than directly referencing the 'Inserted' table?? As far as I'm concerned I am only updating one row so there should only be one row in the 'Inserted' table. Surely creating my own temporary table from the 'Inserted' table should actually take longer but oddly it doesn't.
Also, another thing that seems strange is that my original update to the tblPoliPolicy table is not even updating the 'PoliInstalments' field so why is this code in the trigger even being evaluated at all?? I have proven that the update of the PoliSettlementDueDate code is being evaluated at trigger execution time by simply replacing it with the following...
SELECT *
INTO #Inserted
FROM Inserted
IF UPDATE(PoliInstalments)
SELECT 1
With this code in place, the update runs instantaneously (<50ms). I am confused! If I uncomment the rest of the trigger code in my update trigger which all contains similar code to my second example (all of which is within UPDATE(field_name) logic) then a single update to the tblPoliPolicy table takes over 3 seconds even if none of the the UPDATE(field_name) fields have been updated (thus no trigger code should actually run!). This is obviously unacceptable to a user when the update to this table is only one of a number of updates made to the database from a single user action.
Can anyone possibly explain to me what goes on behind a trigger and why it behaves like this, and if there is any way to improve performance...or is this just a quirk of using triggers? Currently I can only see one way forward and that is to ditch the triggers and go back to manually updating these fields from within the stored procedures themselves.
Any help/advice would be appreciated.
Thanks,
-
How many records in tblPremPolicyPremium?
Which version of SQL?
Do you have recursive triggers switched on?
-
SQL2000 + Service Pack 3a
Recursive Triggers are switched OFF
-
Will have a go at it tonight but for starters I'll limit the "group by" statement to the inserted table data only.
Other words "group by" only on the data you need, nothing more.
How many records in these tables?
Personally I do like the "IN" clause will try and remove that aswell.
-
Sorry should have typed :
Personally I not do like the "IN" clause will try and remove that aswell.
-
The table has approx 60,000 rows so not a vast amount. As for the use of the IN, I also do not like using it unless I have to but if I do the following...
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = (
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID)
FROM Inserted
WHERE tblPoliPolicy.PoliID = Inserted.PoliID
...then an update on the tblPoliPolicy table takes on average 650ms which is 150ms longer than if I use an IN subquery, so in order to maximize performance (although it is still pretty dire) it seems I have no choice but to use the IN method.
Thanks for looking at this one for me - I appreciate your help.
-
How about :
Set Nocount On
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = Min_PremInstalmentDate
FROM
( SELECT PremPoliID , MIN(PremInstalmentDate) as Min_PremInstalmentDate
FROM tblPremPolicyPremium T1
inner join Inserted I2
on T1.PremPoliID = I2.PoliID
and T1.PremDeleted = 'N'
AND T1.PremIncludeInTotal = 'Y'
GROUP BY PremPoliID ) NTE
WHERE tblPoliPolicy.PoliID = NTE.PremPoliID
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I had a similar trigger problem before - the update trigger was being fired even although the physical update on the table was updating 0 rows. This meant that the trigger code was executing even although there was nothing in the deleted or inserted tables. The query starting taking a few seconds rather than the expected few milliseconds when there was data to update - something to do with table scans ?
I was surprised by this but got around it by doing a pre-select count of the number of rows that would be updated. If it was greater than 0 then I progressed to the update statement and the trigger update by default otherwise I bypassed the update statement.
This solved the problem - I still don't know why I would want a trigger to fire when the insert/update/delete that fired it affected 0 rows. I guess that there will be someone some place where this may be seen as an error and the trigger is a place to raise it ?
-
Can you try alzdba example.
Add "if (Select count(*) from inserted)>0" as Alan Palmer suggested.
Please provide feedback on how your ms improved.
-
Thanks for trying to help guys. I am still battling away and trying to avoid going back to putting the trigger code in SP’s but I have to improve the performance soon and am beginning to run out of time in which to experiment. OK, so here are the results from the suggestions I have been given...
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = Min_PremInstalmentDate
FROM
( SELECT PremPoliID , MIN(PremInstalmentDate) as Min_PremInstalmentDate
FROM tblPremPolicyPremium T1
inner join Inserted I2
on T1.PremPoliID = I2.PoliID
and T1.PremDeleted = 'N'
AND T1.PremIncludeInTotal = 'Y'
GROUP BY PremPoliID ) NTE
WHERE tblPoliPolicy.PoliID = NTE.PremPoliID
...runs in 600ms, so 200ms longer than my original update that uses the IN subquery. The suggestion about checking the count of the 'Inserted' table is good idea but I *am* actually updating a single row...it is just that the update is on a different field to the PoliInstalments field. I still don't understand why on earth this code is being evaluated at all when the only field in the row I am updating is the PoliStatus field. I tried anyway just to see the results and this is it…
IF (SELECT COUNT(*) FROM Inserted) > 0
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = (
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID)
WHERE PoliID IN (SELECT PoliID FROM Inserted)
...runs in around 480ms so still slower than no check at all...and if I use a temporary table…
SELECT *
INTO #Inserted
FROM Inserted
IF (SELECT COUNT(*) FROM #Inserted) > 0
IF UPDATE(PoliInstalments)
UPDATE tblPoliPolicy
SET PoliSettlementDueDate = (
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID)
WHERE PoliID IN (SELECT PoliID FROM #Inserted)
...the update takes around 420ms. The more I try different things the more confused I am getting. The conclusion I have come to so far is that no matter what, storing the results in a temporary copy of the 'Inserted' table and working with the data from that is faster than working directly on the 'Inserted' table (even though it only contains 1 row of updated data!?).
We have a number of different servers all with the same database structures on and although some of the servers are quicker than others (due to better hardware/resources), all are exhibiting general poor performance results with the triggers that have been implemented so I would probably rule out any problem with a server itself or the installation of SQL Server.
-
can you post the full ddl of the table/indexes/triggers ?
Just to check : There is an index defined for PoliID ?
Is there a clustered index defined for the table ?
What are the table-stats ?
And indeed, a trigger fires, even if the desired table statement did not affect any rows !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
""And indeed, a trigger fires, even if the desired table statement did not affect any rows ! ""
Indeed that's why you should always do an inner join on the deleted or inserted table so that you trim down the number of rows that the trigger will have to process. Also since you are doing an update in the trigger you can also add this little where condition that will improve even further the performance (not sure you will see a big difference in this case but it doesn't hurt to try).
Update MyTable set MyTable.Field1 = Ins.Field1, MyTable.Field2 = Ins.Field2... from dbo.Mytable Inner join Inserted Ins on MyTable.PK = Ins.PK
/*this is where you get more performance.. and the larger the sets the better the improvement. If you fire up the profiler you will see that the cpu use will be higher with the where condition but the writes will also go down (this is where the trigger loses the most time because hd operation are slower than ram operations.)
*/
WHERE (MyTable.Field1 Ins.Field1 OR MyTable.Field2 Ins.Field2...)
-
As far as cutting down the number of rows processed, that is effectively what I am doing with...
WHERE PoliID IN (SELECT PoliID FROM Inserted)
...(PoliID is the Primary Key field) I have tried using an INNER JOIN on the ‘Inserted’ table instead but it causes the trigger processing to take even longer for some reason. The following statement is also present at the top of the trigger…
IF @@ROWCOUNT = 0 RETURN
…which I assume will abort running of the trigger if no rows at all were updated.
The tblPoliPolicy table has a Primary Key on the PoliID field (non-clustered – the clustered index is on another field). There are also another 24 non-clustered indexes on this table. I have done a DBCC REINDEX on the table but it hasn’t improved anything. The table has over 200 columns and I am beginning to wonder if a table structure this huge is somehow affecting the processing of trigger code. My personal opinion/preference would be to split this table down into smaller one-to-one tables but the table has existed in this state for a long time (before I even joined the company) and is referenced from a whole load of places so splitting it up would be a mammoth task, probably a project in it’s own right. There doesn't appear to be any other performance issues with a table this size - it was only after trigger code was added that the performance seriously declined.
As I am now constrained by timescales and pressure from the powers above to get the performance issue sorted quickly, I have started stripping out the trigger code and putting it in all the stored procedures that perform inserts/updates on the tblPoliPolicy table. I know this probably isn’t the best solution but by doing so I have already noticed a dramatic improvement. A single update to the tblPoliPolicy code with ALL the original trigger code implemented used to take over 3 seconds. With two thirds of the trigger code now stripped out and put into stored procedures the update time is already down to under 1 second, and there is still trigger code left to remove. I only intend this to be a short-term fix so when I have got the performance of the live system back to a usable state, I will then try and do some more investigation.
In the meantime I appreciate the help that everyone has tried to give me with this problem.
-
Are there other users in the db/tb when you run your test(s).
Because you are performing another tableaction
SELECT MIN(PremInstalmentDate)
FROM tblPremPolicyPremium
WHERE PremPoliID = PoliID
AND PremDeleted = 'N'
AND PremIncludeInTotal = 'Y'
GROUP BY PremPoliID
You might run into wait-times, Locking, ... if sql is performing a tablescan or index-scan to perform this action and others are holding the needed pages.
SQLperfmon may help you to identify these kind of bottlenecks.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The database I was doing all the testing/analysis on was a copy of a live database on one of our development servers. I was the sole user of the database so all the performance figures given are for a single user accessing the database which elimates any locking, waiting issues etc.
I have now since dropped all triggers from the tblPoliPolicy table and placed code that does the same thing as the trigger code did into any stored procedures that do inserts/updates/deletes on this table. Not really what I wanted to do as it is messy but the performance has now increased ten-fold and a single update on the tblPoliPolicy table now takes less than 20ms which is the sort of performance I would expect. The changes have been run into the live systems and the users are overjoyed at the huge increase in performance.
Until I can fully understand what goes on behind the scenes with triggers I am going to stay away from using them.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply