SQL Ridicu-List: Let He Who Is Without Sin Cast The First Stone
Kerry Osborne encountered a fairly odd SQL query which performed a LIKE comparison without actually using the LIKE preposition. If there was a SQL “Ridicu-List” it would easily make the cut. Let he who is without sin cast the first stone.
But most of us are guilty of the same sins. The lesser sin of the programmer was the crime of ignorance but few of us are all-knowing. The greater sin of the programmer was to ignore the performance aspect but who amongst us has not done the same on at least one occasion? Jonathan Lewis recalls a teachable moment when he brought a production database to its knees by querying a fixed table.
Some years ago, I wrote an article for the NoCOUG Journal in which I listed five “dangerous beliefs” centered round the theme that programmers have no responsibility for the performance of SQL statements:
Dangerous Belief #1: DBAs bear chief responsibility for the performance of SQL statements.
Dangerous Belief #2: Applications should be designed without reference to the way data is stored, e.g., index-organized tables, hash clusters, partitions, etc.
Dangerous Belief #3: Application programmers should not tailor their SQL statements to make use of existing indexes. DBAs should instead create traps to catch badly performing SQL at runtime and create new indexes as necessary to make them perform better.
Dangerous Belief #4: It is not necessary to review the Query Execution Plan of an SQL statement before releasing it into a production environment. It is further not necessary to freeze the Query Execution Plan of an SQL statement before releasing it into a production environment. It is desirable that Query Execution Plans change in response to changes in the statistical information that the query optimizer relies upon. Such changes are always for the better.
Dangerous Belief #5: The most common cause of poorly performing SQL is the failure of the DBA to collect statistical information on the distribution of data for the use of the query optimizer. This statistical information should be refreshed frequently.
There is no one to blame for the prevalence of these beliefs except ourselves since we’ve been spreading them for decades.