We have a database job that runs every night. It takes care of all kinds of tasks. For example, it detects and resolves data problems. It also implements certain time based business requirements. Lately this job has been taking a lot of time to complete.I traced one certain part of the job that was taking all night. It was looping through a ton of records, then doing a bunch of updates on them. The algorithm to do the updates was painfully inefficient. I replaced all of this with single SQL updates of all the records at once. The job that took all night completed in 10 minutes.
The job was doing well for a while. Then it got slow again. Another developer started to look at it. All he came up with was that there were a lot of triggers on the tables involved.
I hate when somebody tries to solve a performance problem and asks if we can add some indexes to make it faster. Ouch. The latest analysis felt like this type of solution. Sure you don't want to do a lot of work in triggers for updates that happen frequently. But triggers, lots of them, or complicated ones do not always mean slow performance.
Performance tuning is quite a deep subject. Maybe we need to send some of our developers to training on performance analysis and tuning. That might get us past the old "add indexes" solutions.
