February 9, 2000 Volume 3 - Number 3
Nordic Nerds and Bugs
When SQL creeps up on you
Iearn part of my living teaching advanced SQL classes to people all over the world. In mid-November of 1999, I spent a week in Helsinki, Finland, at Helia, the business polytechnic. The school could not get 30 copies of my book, SQL For Smarties (Morgan-Kaufmann, 1999) in time, so they asked me to bring more than 30 copies as luggage. So I carried a very heavy suitcase in which there was little room for extra underwear or heavy clothing.
I seem to go to Nordic countries to do this sort of thing every winter, so I usually do not get much culture shock anymore. I stayed at a student hotel with no telephone in the room — everyone in Finland is supposed to have a cell phone. (Bet you thought that Nokia was a Japanese company!) There were no telephone jacks in the classrooms and when I did get to one in an office, I could not configure my modem setup on my laptop to get an outside connection for my email. What I needed for this job was a top-of-the-line international smart modem that would configure itself to the phones. No, an old acoustic modem would not work — it could not accept the Finnish modern telephone handset.
Debugging is Procedural; SQL Ain’t
The purpose of my class is to teach programming tricks in SQL to programmers who have been working with SQL for about a year — long enough to know the syntax and run into real programming problems. The bad news with SQL is that you don’t get traditional debugging tools! The poor programmer is quickly back to the days of “Wright brothers software engineering”: Put it (the query) all together and push it off a cliff to see if it flies.
Such is the nature of a nonprocedural language. You cannot really stop the process at a step controlled by the programmer because the programmer does not create steps in the process — the SQL engine does that, and it might not do the same query the same way when the statistics change.
What vendors call debugging is more often debugging of the procedural language that comes with the particular SQL product (Sybase’s T-SQL, Oracle’s PL/SQL, and so on) or it is really performance optimization. Most 4GLs are pretty simple one-pass compilers, so it is relatively easy to insert the usual breakpoints and variable dumps into their code. But this insertion is not the nonprocedural part of the code.
Performance optimization leaves the query the same, but the execution plan or the indexes get changed to improve performance. Please do not misunderstand; these are all good things to do to code, but they are not really debugging SQL the same way you debug Cobol or a 4GL. Here are some products you might want to look at that can help.
Compuware Corp. has DBPartner, which works with Oracle’s PL/SQL in the context of an application, allowing developers to understand exactly which SQL an application is running on a database regardless of whether calls are explicit or implicit. I haven’t played with it yet, but it advertises a GUI and a toolbox with Join and Index wizards, that make the tuning process a point-and-click one. There is also an analysis display for spotting the most troublesome SQL statements.
IBM is now offering an index wizard. The first release got mixed reviews, but IBM is working on it. I would like to know what both these product use for heuristics, since the choice of picking a secondary index (an index used for speed as opposed to one used for uniqueness) is “NP Complete.” If you are not a computer science major, that means as the problem gets bigger, the time and resources required to solve it get so much bigger, so much faster that you cannot do it for the general case.
You might also want to check the SQL optimization tools from Lecco Tech or get a free trial from its Web site (leccotech.com).
Sylvan Faust Inc. (www.sfi-software.com) has had some great tools for years that will actually rewrite bad SQL. It started with the Sybase family and now has Oracle tools as well.
An old friend of mine, Gus Baird of Georgia Tech, used to tell his students they did not need debugging tools. Programming bugs are not like termites; you put them into your code yourself. Don’t do that and you will not have problems. I have some suggestions for getting the code right the first time, but that is another column.
Joe Celko is an Atlanta-based independent consultant. He is the author of three
books on SQL -- SQL For Smarties (Morgan-Kaufmann, 1995),
SQL Puzzles and Answers (Morgan-Kaufmann, 1995), and
Instant SQL Programming (Wrox Press, 1997) -- and wrote the SQL for Smarties column for DBMS
magazine. You can contact him via email at www.celko.com or 71062.1056@compuserve.com.
