I got to hear Oracle performance guru Jonathan Lewis today in an all-day seminar in Dallas. "Writing Optimal SQL" was part 2 of a two-part series here in Dallas, sponsored by Dallas Oracle Users Group (DOUG).
Jonathan started out by reviewing some general principles, such as the importance of design and optimizing for the global situation (many queries) not just a single query. "Making me go fast might make you go slower," he said.
Performance comes from precision in selecting just the blocks and rows you need to select, and from the structure of the data (where it's located).
My favorite part of the seminar was his discussion on coding style for SQL, a topic that is often ignored in performance discussions. His view that "Computers are cheap; humans are expensive" is one that I've been preaching for years in my SQL and PL/SQL classes. "You have two audiences," I tell my classes, "the compiler, and the poor person who comes after you to maintain your code. Be kind. Someday that person will be you." Jonathan's take is that you should write SQL as a close translation of the business purpose of the query, and only fiddle with it if performance isn't acceptable.
He also suggested: "Don't tune the SQL; tune the end users," meaning that if a requirement is unclear or the data creates unexpected results, go back to the person requesting the report and get clarification instead of burning a lot of time over-engineering the solution to fit every possible case.
This talk could easily have been titled, "Unintended Side-Effects of SQL" because Jonathan went through many scenarios in which results differed from what might have been expected, and why it turned out that way.
It was a great session. Thanks to Speak-Tech and to DOUG's Mary Elizabeth McNeely, president and programs chair, for bringing Jonathan Lewis to town.
View Bob Watkins's profile