Speed vs Maintainability vs Flexibility

One of my main remits at my current client is performance tuning and mainly at the application SQL and PL/SQL level (where most gains are to be normally made).

The primary tools for this are EXPLAIN PLAN, extended SQL TRACE, DBMS_PROFILER and RUNSTATS.

Whilst there will be some missing indexes and some benefits in rewriting queries and routines to work with better set operations, it has quickly become obvious that the biggest benefits will almost certainly come from taking all the lookups that have been packaged into nice, reusable function calls and bringing them back in line in the queries with appropriate set operations.

There are lots of function lookups in the SQL. And these, in themselves, are well written and make lots of sense. But the trouble is that hundreds and thousands of little row-by-row lookups can kill your performance.

 It’s also worth noting that a simple explain plan or autotrace won’t reveal the cost of this whereas a sql trace of a session and a dbms_profiler run against the procedure will give a much better picture of where all the problems are.

But, even so, it’s not as if these lookups are wrong as such. It’s just that in these circumstances speed has become more of a priority over maintainability and reuse.

About these ads