Monday, April 06, 2009

Spinning wheels

Have you ever had one of those days where you are doomed to spinning your wheels working on a problem?

I have been working on improving one of our database triggers to do a single MERGE statement instead of opening two cursors, doing an insert and then doing an update of the same record. I kept getting a PL/SQL: ORA-00969: missing ON keyword compilation error. Can I not LEFT JOIN something to DUAL? Can I not use a :new.column variable within a function in that MERGE? I tried the usual reworking of the statement a half dozen times.

I tried a Google search.

Most arguments argued that it was just one record and to keep things simple and just do the update (or insert) and then handle the error to perform the other DML event. Yeah, it is only one record and the extra statement parse overhead is negligible in the grand scheme of things. Then again, I am an old mainframe system geek that remembers every little bit counts when dealing with high volume processing. Back in those days shortening an eye-catch in working storage by a couple bytes could end up saving 4K in a C.I.C.S. page. Do that for a couple dozen programs and you might end up increasing response time because of fewer space compressions. I see that methodology in practice; making things easy for the application developer at the expense of performance. It is a fight that is being lost to the get it done quickly, take credit, and make yourself and your resume look good crowd.

Then again, I could have used a code generator or some drag and drop development environment and it might have saved me a couple of hours of work by adding the required parenthesis around the condition on the ON clause before the merge update and merge insert clauses. I could have saved a couple hours of my valuable time. It is all about finding a good balance between doing what is best for performance and sustainability of hardware versus making our lives easier.

No comments: