Deja Vu: Five Dangerous Beliefs About SQL Performance

Here’s an edited transcript of an instant messaging conversation I recently had with a colleague (DBA 1 in the transcript); identifying information has been removed.

DBA 1 (7:43:08 AM): Iggy, can u check our db, many locks
Iggy  (7:44:40 AM): ok checking
Iggy  (7:46:09 AM): only two sessiosn in that state now
DBA 1 (7:46:14 AM): [DBA 2] killed some sessions, looks betrer now
Iggy  (7:46:19 AM): right
Iggy  (7:50:07 AM): ok i'll watch the database for a minute but it looks liek things are better now
DBA 1 (7:50:40 AM): yes
Iggy  (7:52:48 AM): i sent you one of the blockign statements
DBA 1 (7:54:44 AM): yes, that's the one [DBA 2] killed
Iggy  (7:55:50 AM): it is taking 4 minutes
DBA 1 (7:56:19 AM): yes, very slow, can u look at this query see if we can tune it?
Iggy  (7:56:51 AM): hmmm there have been changes to the query recently?
DBA 1 (7:57:07 AM): this is a new query
Iggy  (7:57:12 AM):  
DBA 1 (7:57:19 AM): deployed over the weekend
Iggy  (7:57:22 AM):  

Some years ago, I wrote a series of articles (SQL Sucks!) in which I listed five dangerous beliefs centered round the theme that SQL programmers have no responsibility for the performance of their SQL:

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.

I concluded the series by saying: “The promise of relational technology was that application programmers would be relieved of the responsibility for the efficiency of the SQL statements they write. We have argued that this promise is a failed promise and that there are tremendous barriers preventing it from ever being fulfilled. And so, therefore, our take home message is that SQL performance requires conscious effort on the part of the developer!”

There is no one to blame for the prevalence of these beliefs except ourselves since we’ve been telling SQL programmers for decades not to concern themselves with performance.

P.S. A great explanation for why the promise of relational databases was not fulfilled is a blog post by Christopher Charles titled Databases – My Brilliant Career Tuning SQL.

SQL Sucks! Part I
SQL Sucks! Part II
SQL Sucks! Part III

About these ads