Performance Tuning

Recently I got a trouble ticket from a high-profile user. An application in our suite was having all kinds of performance problems. Normally I pass performance problems to our Performance Engineer. But I also do some upfront legwork to help isolate the source of the problem first.

The description in the trouble ticket did not make a whole lot of sense. Rather than waste time guessing about this, I called the user directly. During our chat I determined which user operations were slow, and how long each one took.

Initially I was unable to replicate the problem in a development environment. So I queried the Production database to check the volume. Aha! Production had over 50,000 records. My test set only had 1,000. So I wrote a small PL/SQL script to generate test data that matched the Production volume.

Now I was able to experience the performance problems in development. Next I did some profiling to figure out where the app was spending all the time. Turns out that adding, deleting, and editing records were very slow. But the delay was not in the SQL code. The problem stemmed from a poor desin. After each add/delete/edit, the whole data set was reloaded and reprocessed from the database.

I am currently regression testing smarter add, delete, and edit operations. Got to make sure I didn't break any functionality with the fix. So far a peer review of my first cut has shown that a couple things are broken.