Monday, December 13, 2010

Another crapplication

It never ceases to amaze me how software that sells for five figures or more can contain so much shit code. My most recent example: I noticed a CPU hogging process running on our Oracle server and was astonished at two things. First thing was a SELECT * in a static query containing eight inner joins. The second item was that same statement (and over 3000 almost identical statements) was not using a bind variable in the WHERE clause for the primary key (a VARCHAR2(254) containing a GUID).

There is no excuse for production code containing a SELECT * from any number of tables. What made this example even more inexcusable was the table contained a BLOB and judging by the pattern of other SQL statements these queries were pulling back a description and last updated by date.

There is only one excuse that I know of to NOT use bind variables in prepared statements. To be honest, I do not know if this is one hundred percent true of Oracle or other databases, but if the query is selecting by status or other column where the difference between the counts of the values is extreme then it would be best to not use a bind variable on that status. If a table contains a billion orders where status is filled and a couple hundred with a status of cancelled and the query is selecting only by status then using a bind variable might not provide an optimal plan for both situations (it might use a full table scan on filled and index for cancelled). In the above example, the software was creating, parsing, opening, fetching then closing the same logic SQL thousands of times when it should have created, parsed once then bound and executed (reused) thousands of times. It was a single row select by primary key (a GUID even) and that should always be done using a bind variable.

The developers might not be at fault here. The above scenario reeks of poor, generated, object oriented code. Get the primary keys for all of your data objects then read them one by one to display on some dumb-ass web page. More likely: read incoming XML data stream into business logic tier then instantiate all the needed business and data turds then have the business turds use the data turds to retrieve data using the exact opposite of a single, bound, efficient SQL statement, then convert the results of all the turds to XML so the rendering engine can puke it out to a web browser, iDroid, dumb phone, really dumb phone, tablet, pad, tampon, car dashboard, HAM radio or intelligent kitchen appliance.

What frustrates me is that I am seeing this crap in a pricey application. I would be tolerant it were coming from some schmuck trying to write an application to pay his (or her, no wait, probably his) way through college. This application has numerous customers, all of which are suffering with this crapplication and the unlucky system administrators are stuck between the bean counters and the vendor when attempting to tune the piece of weasel poop.

A good Ask Tom article