Performance Tradeoff

I remember the first huge PL/SQL stored procedure I wrote. The thing was highly modular. It came out to maybe 10k lines of code. I liked that no procedure or function was too long. I broke things down into small pieces. Did a lot of testing to ensure the functionality was correct. Then I shipped it to a production environment.

 

Immediately I got complaints that my stored procedure took too long to complete. The thing might have been running for almost an hour. Yeah. The data I was processing was on the order of millions of rows. I had to do a lot of crunching to determine the outputs I had to produce. Nobody told me about the performance requirements.

 

I received some help from some performance tuners. They had some tricks to make my jobs run in parallel. They rewrote some of my long running queries. Some problems such as many SQL statements to ensure modularity could not be fixed without a rewrite. Ouch. In the end, I think the average time of my runs was brought down to 20 minutes. It was acceptable but not great.

 

Now I try to produce code that runs fast. From the get go, I code in a way that does not duplicate SQL. I try to get everything done in minimal large SQL select statements. However there comes a time when I might need to duplicate a SQL query to ensure the code base is maintainable. It is a delicate balance.

Errors in Report

A tester informed me with a number of problems with one of the reports out code produces. One specific problem was that the title of the report indicated there were errors. The report itself was not that big. The SQL was a bit complex.

I asked for some logs from the server. When I poured through them, I found references to an ORA-14551. That error indicates some code is trying to do some DML in the middle of my SELECT statement. I did not see any explicit UPDATE/INSERT/DELETE statements.

Then I went to the end of the procedures called by the report. Turns out the exception handler tried to add some records to the database. Ooops. You can't do that in the middle of a SELECT statement. I think I could just push that error handling down to the report, out of the stored procedure. Problem solved.