by Stéphane Faroult
I have been poring over an Oracle procedure of death in which a loop over a complex cursor calls a no less complex function in which multiple SELECT statements set various ancillary variables before a row insert accompanied by an UPDATE of the corresponding row of one of the tables referenced in the initial cursor. Still with me?
I have several times heard that procedures and simplistic SQL statements make for easier-to-maintain programs. I am not that sure; my only certainty is that it makes it easier to assign more junior, and therefore cheaper, developers to the task. I am under the, possibly false, impression that to most young graduates GROUP BY represents the ultimate level of SQL sophistication.
But complexity isn't inherent to a language, whether it is SQL or a wrapper language to embed SQL statements. Complexity is born of business requirements, and usually
made significantly worse by poor database design.
I am not sure that a long succession of if ... else if ... else if ... embedding a number of SQL statements makes for an easier read than a CASE construct and a handful of outer joins. Actually, my feeling is that the larger the number of lines, the longer it takes me to grasp what the damn thing is meant to do.
But what I am certain of is that if we don't replace this
procedure with an INSERT ... SELECT and a trigger to update the other table, we won't be able to go much faster than the 150,000 rows per hour that are currently processed.
The first rule is...
...never do in PL/SQL that which can be done in pure SQL.
My favorite is a SQL select that returns lots of data to the handler program in perl, and then in perl,we calculate a bunch of sums, counts, and averages on that original select.
So far, it seems like the more I can push to the database for it to calculate for me, the better. Are there any calculations that are more efficient in the wrapper perl/php/python/java code than in SQL?