Performance Tuning Triggers

I had to test some fucntionality that essentially hands with large volumes. The first test ran for over 4 hours. That's when I killed it. I was hoping it would eventually finish. No such luck. I took the sample size down by 99%. The thing ran for 30 seconds. Then I bumped up the sample size 10 times. It ran for 10 minutes. That's not linear.

There was mass hysteria on the team about there being many triggers on the table getting updated. I did a performance test, enabling only one of the triggers at a time. Turns out only one trigger causes any performance change. I studied the code of that trigger, and found that it updated a lot of records for each update. Not good.

My rewrite was to disable the trigger during the large update. However I did not want to disable the trigger for all users, just the one doing the update. There seems to be no Oracle support to disable a trigger per session. So I used Murnane's technique to implement a disabled trigger per session. I tested this out with some profiling SQL scripts. Now I am putting this in the application. Let's hope for speedy returns.