The Art of SQL

Strategies for Conquering Bad SQL

Can database design be likened to a military campaign? It can, contends author Stephane Faroult in his new book, “The Art of SQL” (O’Reilly), written with Peter Robson. “Teaching how to use a language is difficult enough; but how can one teach how to efficiently use a language?” asks Faroult. “I have a natural tendency to consider every new performance challenge as a battle to be fought against an army of rows, and I realized that the problem of teaching developers how to use databases efficiently was similar to the problem of teaching officers how to conduct a war.”

How so? As Faroult points out, “You need knowledge, you need skills, and you need talent. Talent cannot be taught, but it can be nurtured. This is what most strategists, from Sun Tzu, who wrote his ‘Art of War’ twenty-five centuries ago, to modern-day generals, have believed.” Just as generals try to pass on the experience they’ve acquired on the battlefield, so Faroult attempts to apply this method to more peaceful aims: specifically, writing good SQL code.

For all the buzz about trendy technologies, the processing of data is still at the core of our systems, and all the more so as the volume of data that must be managed seems to be increasing faster than the speed of processors. “The most vital corporate data today is stored in databases and accessed through the imperfect, but widely known, SQL language,” observes Faroult. Database performance has become a major headache in many companies, further confounded by the belief of most IT departments that developers should provide simple SQL code to solve immediate problems, and that ‘bad SQL’ should be give to senior database administrators to tweak and make run faster, with the help of a few magic database parameters.

This apparently commonsense, safe approach ends up creating more headaches, Faroult argues. “Writing inefficient code and relying on experts for tuning the ‘bad SQL’ is actually sweeping the dirt under the carpet.” Faroult sees SQL issues as encompassing much more than the proper writing of a few queries. In his opinion, the first ones to be concerned with performance should be developers. Good code must be fast and sound from the start, and that requires a firm understanding of SQL and relational theory.

“The Art of SQL” offers best practices that teach experienced SQL users to focus on strategy rather than specifics. Following Sun Tzu’s outline–including the use of most of his titles–Faroult and Robson have divided the book into twelve chapters, each containing a number of principles and maxims. Chapters include:

-Laying Plans: Examines how to design databases for performance
-Waging War: Explains how programs must be designed to access databases
efficiently
-Tactical Dispositions: Why and how to index
-Maneuvering: How to envision SQL statements
-Terrain: How physical implementation impacts performance
-The Nine Situations: Classic SQL patterns and how to approach them

“The Art of SQL” also provides concrete advice to help readers cement their positions. “This is not a book to dabble with or pick the odd page here and there,” says Robson. “Maximum value will be found by taking the time to read the chapters carefully. If readers are able to obtain performance improvement by rewriting their SQL, where previously they would have purchased new hardware, then the book is justified one hundred times over.” According to Robson, this is unquestionably realistic.

Faroult adds, “The book will not turn bad programmers into good programmers. But I have met many good, intelligent programmers who ignored everything of databases and whose projects tottered on the brink of catastrophe because of their lack of understanding of database issues. Usually people learn by trial and error, and it’s when they become reasonably competent that they move into management positions and are replaced by inexperienced developers. One of the purposes of the book is to make good developers leverage their potential at an earlier stage.”

After having studied the book, readers may still write awful, costly queries. “One sometimes has to,” says Faroult. “But hopefully it will be knowingly and with good reason.”