A job that runs at night has been taking too long every night this week. This causes pain as the system administrators call up our DBAs or help desk in the middle of the night. Obviously we have a problem. I would call it a performance problem. What makes this unique is the response I heard from this. Developers told me a manager decided there would be no new database triggers implemented due to the performance problem.
WTF? This made little sense. Who correlated our performance problem to the presence of triggers? I attended a meeting hosted by this very manager. So I asked about this new "no triggers" edict. He said that he has inspected the PL/SQL source code. He found many database triggers. He also saw triggers firings that caused other triggers to fire as well. Therefore they were suspects in the performance problem.
I said that by this logic, we would find many stored procedures in our source code. The stored procedures get exectured. The code runs slow. Therefore we should not use stored procedures. Obviously this argument does not hold up.
Rather than being an obnoxious developer who shoots down ideas, I decided to later pitch a plan to get to the actual root cause of the performance problem. We should find out what pieces of the code are taking a long time to execute. Then would should drill down into the slow pieces and find out why. If we do that and find some database triggers to blame, I agree we should optimize them.
I come to find that there is a proposal to do the very thing I recommend. Our manager clarified that we cannot add database triggers without analyzing the performance impact. I am all for that. I run timing studies all the time on slow code. We need to use logic and hard evidence to deal with tough and frustrating problems such as performance. If not, we might as well chirp "add more indexes" like any other lackey and get nowhere.
