June 19, 2020 at 1:54 pm
Hi All,
I have one table in that 10 triggers are there, due to this, it takes more time while insert and updates record in the table.
1. For Insert triggers - 4
2. For update triggers -6 (On different columns).
Can anyone know how to sort this issue?
Can I merge triggers in one single For update or For Insert trigger, means is it beneficial in term of performance
Kindly replay if anyone knows.
Thanks in advance.
June 19, 2020 at 2:00 pm
Yes, I would expect a single, optimised, trigger to perform better. The best way to confirm this, of course, is to make the changes in a test environment and check for yourself.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 19, 2020 at 4:58 pm
Technically, you could even bump that down to 1 trigger instead of 2 by having it as a for update AND insert. Doing it as 1 trigger might not help performance (may hurt performance actually) as you then have to bake some logic in to check if it is an insert or update.
I do agree with Phil that reducing the number of triggers and optimizing them will give better performance. On top of that, removing the trigger and updating the stored procedure that does the insert could even take that a step further in performance improvements.
Either way (as Phil said) testing is critical to this. Test current state multipile times to get a good estimate of performance, and test multiple times with the new solution. begin transaction and rollback transaction will be your friend if you need to modify data - it will hurt the performance metrics, but will help prevent data changes so you can ensure performance changes are not due to data changes. Had a SQL query that when run on live or dev environments completed in under 2 minutes. Ran it on the test environment and it was nearly a 3 hour wait. Spent a bit of time confirming the environemnts were configured the same OR that the test environment was better (test had more max memory than dev, but less than live). Was not running out of memory; the problem was data differences. Added 2 indexes onto test and it completed in under 2 minutes like dev and live. This change did not need to propagate up or down to dev and live as they performed good enough for this task and next data refresh of test will correct the issue. Just a cautionary tale about how data changes can cause performance differences in a query when doing performance tuning.
Last thing to note about triggers is that they fire 1 at a time, but in an order determined by SQL at the time it is fired. You can force the order (somewhat) and it is discussed more here:
https://www.mssqltips.com/sqlservertip/1409/forcing-trigger-firing-order-in-sql-server/
By having fewer triggers (1 or 2), you can control the order of operations on the trigger much better.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply