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.
