Range-Keyed Queries: In this O’Reilly-sponsored article, I describe strategies to handle a recurring class of problems where part of the table key is a plain-vanilla ID, but another part of the key specifies a range, most-commonly a date range that specifies the dates for which the record applies. For example, time-dependent historical data is frequently specified with the date on which the record first appeared, and the date on which the record ceased to be current.
Wrong-Errors
Bugs: A New Class of Bug: In
this O’Reilly-sponsored article, I describe ways to eliminate a new class of
bug I’ve described, dubbed the “wrong-errors bug,” and I propose RDBMS
enhancements that would make these bugs much less common and problematic.
Why Good Optimizers Do Need a Hint: This is a longer version of my part in the “Ask the Oracles” Discussion of “Does the Optimizer Need a Hint” in the Q3-06 journal of the Northern California Oracle Users Group.
When
Good Optimizers Make Bad Choices (Sometimes): In this presentation to the Northern California
Oracle Users Group (NoCOUG), I described why human SQL tuners can still “beat”
cost-based optimizers at the “game” of SQL tuning, by exploiting enormous
“unfair” advantages that human tuners have over even the best optimizers. The
presentation is accompanied by a White
Paper and files
with supplementary material, including all of the material necessary to
reproduce the examples presented. This is an enhanced version of a presentation
I gave earlier, at the 2005 Hotsos Symposium.
Fixing Broken SQL: In this presentation to Hotsos, I presented material with the following abstract: Most developers spend most of their time maintaining code they did not originate. Frequently, developers must fix SQL without knowing its business purpose; this is especially true of performance specialists, who often work on SQL for applications, or parts of applications, that are unfamiliar. Surprisingly, there are patterns in most incorrect SQL that you can learn to recognize and repair even without knowing the purpose of the SQL. This presentation reviews a breakdown of the most common recognizable patterns in broken SQL, and teaches how to find and fix what’s broken with minimal information apart from the bare SQL. The resulting repairs frequently enable better performance, usually fix corner-case functional defects, and invariably make the SQL clearer and easier to maintain. For tuning specialists, an understanding of these patterns enables an unexpected bonus service—while tuning someone else’s SQL, you also find and fix a subtle functional problem they did not even know existed.
Getting SQL Performance Right the First Try (Most of the Time): In this presentation, I show how a set of best practices can enable a development organization to avoid most of the pitfalls that lead to poorly-performing SQL, SQL that has subtle functional defects, and SQL that is hard to maintain. Following these practices, we can usually "get it right the first time," without iteration, for large time savings in the end. Here is the whitepaper that goes with the presentation. This seminar is a very brief version of the material that I present in my new one-day class of the same name.
A Taxonomy of Problematic SQL: In this seminar, I present a formal way to classify most of the SQL that proves problematic from both a performance perspective and a functional perspective. Armed with this classification scheme, it is much easier to recognize problems and their solutions. Here is the whitepaper that goes with the presentation.
Natural Data Clustering: Why Nested Loops Win So Often, with whitepaper, presentation first given at the HOTSOS seminar in Dallas, in March 2008. I discuss here the reasons why nested loops perform better than cost calculations usually conclude, compared with hash and sort-merge joins.
SQL
Tuning, Reaching Recent Data Fast, with whitepaper,
presentation first given at the OAUG in
©2008 Dan Tow, All rights reserved