Abstract:

If you're programming applications that store data, then chances are you're using SQL, either directly or through a mapping layer. Well, here's a sad secret - most developers aren't SQL experts, and most of the SQL that gets used is inefficient, hard to maintain, and sometimes just plain wrong.

Source:

B. Karwin, SQL Anti-patterns Avoiding the Pitfalls of Database Programming, The Pragmatic Bookshelf, 2010. ISBN-10: 1-934356-55-7

Summary:

This book lists some common antipatterns found in database programming.  Chapter 18 mentions an anti-pattern called "spaghetti query", which is a common issue in large stored procedure development.  A single sql query attempts to do too much work in one statement, which makes maintainability and troubleshooting exceedingly difficult, and can cause poor performance.

Solution Extensions:

We can incorporate this pattern into any potential refactoring to reduce complexity.  The author mentions that queries that fall into this pattern are difficult to read, difficult to write, difficult to enhance, and it makes it much more difficult for the SQL engine to optimize and execute.

Relevant Quotes:

(Chapter 18)

Antipattern: Solve a Complex Problem in One Step SQL is a very expressive language—you can accomplish a lot in a single query or statement. But that doesn’t mean it’s mandatory or even a good idea to approach every task with the assumption it has to be done in one line of code. Do you have this habit with any other programming language you use? Probably not.

Unintended Products One common consequence of producing all your results in one query is a Cartesian product. This happens when two of the tables in the query have no condition restricting their relationship. Without such a restriction, the join of two tables pairs each row in the first table to every row in the other table. Each such pairing becomes a row of the result set, and you end up with many more rows than you expect.

18.1 Objective: Decrease SQL Queries One of the most common places where SQL programmers get stuck is when they ask, “How can I do this with a single query?” This question is asked for virtually any task. Programmers have been trained that one SQL query is difficult, complex, and expensive, so they reason that two SQL queries must be twice as bad. More than two SQL queries to solve a problem is generally out of the question. Programmers can’t reduce the complexity of their tasks, but they want to simplify the solution. They state their goal with terms like “elegant” or “efficient,” and they think they’ve achieved those goals by solving the task with a single query.

The Law of Parsimony When you have two competing theories that make exactly the same predictions, the simpler one is the better 

Writing SQL Automatically—with SQL When you split up a complex SQL query, the result may be many similar queries, perhaps varying slightly depending on data values. Writing these queries is a chore, so it’s a good application of code generation. Code generation is the technique of writing code whose output is new code you can compile or run. This can be worthwhile if the new code is laborious to write by hand. A code generator can eliminate repetitive work for you.