An Interview with the Authors of SQL Performance Tuning
TRUDY: Our test programs for the book all use either ODBC or JDBC, since they’re supported by all eight of our DBMSs. Generally the support is excellent, but here are some ODBC items that we had trouble with (using default drivers):
– [SQLBindCol] One DBMS refused to handle SQLBindCol unless the previous operation ended with SQLFreeStmt(SQL_UNBIND) and SQLFreeStmt(SQL_RESET_PARAMS). One DBMS crashed if indicator variables were used. One DBMS didn’t detect TINYINT values that were out of range. One DBMS returned an error when the number of bound columns was greater than the number of result columns.
– [Autocommit flag] One DBMS won’t execute CREATE/ALTER/DROP unless autocommit is on.
– [SQLColumns] One DBMS can’t process this function correctly.
And here’s a sample of some SQL syntax that we had trouble with in our tests, because at least one DBMS showed anomalous behavior:
– Floating-point literals: we had to use 7.0E05 rather than 7.0E5.
– Views: we had to use “CREATE VIEW v (x) AS SELECT column1 …” rather than “CREATE VIEW v AS SELECT column1 AS x …”.
– Stored procedures: we had to put IF and WHILE conditions inside parentheses.
– Column names: we had to use lower case consistently.
– Function names: we ran into several variant spellings, e.g. “SUBSTR” instead of “SUBSTRING” and “CURRENT DATE” instead of “CURRENT_DATE” … POSITION, CASE, and MOD are non-portable.
– Table names: one DBMS uses the same namespace for both tables and procedures, so we couldn’t have a table named X if there was already a procedure named X.
– Constraints: we couldn’t always insert NULL into a column if there was a constraint like “CHECK (column1 < 0)”.
– Integers: the minimum SMALLINT value in one DBMS is -32767 rather than -32768.
– Set functions: we had to use COUNT(*) not COUNT(literal).
What are some of the best tools that SQL developers can use in order to help them identify poorly performing SQL code, and then to tune it?
PETER and TRUDY: These are examples of what we — and the book — won’t help you with! They will depend on the DBMS, on the installation, on factors we didn’t study, and on comparative evaluations which we promised to avoid.
What’s the best way (or ways) for readers to get the most out (mastering SQL performance tuning) of your book?
TRUDY: Everyone we’ve talked to said the book is an easy read. So I hope there’s no requirement for a manual. Just sit in a tub and turn to page one.
Besides your book, what other database tuning books or other resources do you recommend?
PETER: Your SQL-Server-Performance.com recommendations are on the mark, and I especially recommend Inside Microsoft SQL Server 2000. There are also tuning books for Oracle and DB2. Other DBMSs have very little performance-related literature outside the vendor manuals. I know of only one Sybase book, one (upcoming) MySQL book, and nothing for other DBMSs that we covered. I’ll repeat that every DBMS needs its own specialist book, and we want to be symbiotic.
TRUDY: Anything at all by C.J. Date. His An Introduction to Database Systems is an excellent place to start.
What’s the one biggest single thing you want your readers to learn from reading your book?
TRUDY: That question’s too hard. I’m reluctant to pick out one item and say “this is the biggest” for fear that people will think that’s the theme, or people will suspect I have a warped sense of priority.
Besides what has already been covered, what else would you like to say or comment on?
TRUDY: I do want to mention that we’ve put up an errata page for SQL Performance Tuning. You can look for errata and information that became available after we went to print on www.ocelot.ca/sqlperferrata.htm.
PETER: The last few weeks since the book appeared have been tremendous, fruitful, ego-building, frantic fun. Thanks to you for your review and for this interview, thanks to all the other folks who’ve commented or critiqued, and I look forward to more of the same.