Who Should Tune SQL: The DBA or The Developer?

I am working on a proposal for my second book—this time, an SQL book—for Apress and have been re-reading my collection of SQL books.

In Oracle SQL High-Performance Tuning, Guy Harrison says:

 “For instance, when trying to tune someone else’s SQL, DBAs will have to expend a fair amount of effort in determining what the SQL is trying to do. They will need to understand the underlying data at least as well as the author of the SQL and may be concerned that in tuning the SQL, they might inadvertently change the semantics (the meaning) of the SQL. Generally, only the author of the SQL has all of the knowledge required to tune the SQL. [emphasis added] All that might be missing is the necessary SQL tuning principles—and that’s the purpose of the book.”

In SQL Tuning, Dan Tow says:

 “I assumed for years that the developers of an application, who wrote the SQL, would always understand far better than I how to fix it. … Here is where SQL delivered me, at least, a great surprise: you do not need to understand other people’s SQL to tune it! [emphasis added] Treat SQL as a spec—a clear and unambiguous declaration of which rows of which tables the application requires at some particular point in a program. … You do not need to know why the application needs those rows, or even what those rows represent. … All you need to know or figure out is how to reach those rows faster, and you can learn this by just examining the SQL, tables, and indexes involved, with simple queries to the database that are completely independent of the semantic content of the data.”

The above seemingly opposite viewpoints can be reconciled. You don’t need to determine what the SQL statement is trying to do if your only goal is to find the best query execution plan for it. But you do need to understand what it does if you’re trying to rewrite it in ways that perform better; completely replacing the SQL statement with a PL/SQL program or a program written in a conventional programming language—such as C or Java—is also one of the options that is available to you.

For example, consider any one of the nine solutions that were found for the First International NoCOUG SQL Challenge. You don’t need to understand what it does if all you’re trying to do is to find the best query plan for it. But you do need to understand what it does if you’re trying to replace it with a faster or more scalable solution.

The first solution—by Laurent Schneider from Switzerland—used the CONNECT BY clause, and the SYS_CONNECT_BY_PATH and XMLQUERY functions.

The second solution—by Craig Martin from the USA—used logarithms and anti-logarithms in conjunction with CONNECT BY and SYS_CONNECT_BY_PATH.

The third solution—by Rob van Wijk from Netherlands—used the MODEL clause.

The fourth solution—by Vadim Tropashko from the USA—used recursive common table expressions.

The fifth and sixth solutions—by Alberto Dell’Era—used advanced mathematical techniques such as convolutions, Discrete Fourier Transforms, and the Fast Fourier Transform.

The seventh solution—by Fabien Contaminard from France—was based on the multinomial distribution.

The eighth solution—by a blogger named Cd-MaN from Romania—used pipelined table functions and recursion.

The ninth solution—by André Araujo from Australia—used binary arithmetic and common table expressions to solve the problem.

My own position is that SQL should be “efficient by design.” The conventional wisdom is that developers need not concern themselves with performance. In a series of articles that I wrote for the NoCOUG Journal (SQL Sucks!—Part I, SQL Sucks!—Part II, SQL Sucks!—Part III), I listed the following “dangerous beliefs”:

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.

What do you think?