Performance Problems

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.

Pains from a Hack

I got assigned to work a curious bug in the system. The customer said a bunch of data was in the wrong state. I called our customer and got some more info. The best help they gave me was some examples in production.

I studied the audits and found the bad state transition. Then I poured over our database packages to find out why this was happening. Then I found the guilty party.

Somebody was making a call to some existing procedures in our packages. They found out they needed to set some data to an intermediate value first for the code to work. However when the code encountered an error, the package code left the data in the bad intermediate state.

Fail. I took out the hack. then I modified the packages to work with the new data pattern. Couldn't the original programmer have done this? Do the hard work up front. It will save everybody some pain. Next I started looking into why there was an error in the first place. That's a story for another time.