SQL Tuning [Electronic resources] نسخه متنی

1.4 A Bonus

The method
this book describes reduces a query to an abstract representation
that contains only the information relevant to tuning.

I often substitute query for SQL statement. Most tuning problems, by far, are queries (SELECT statements, that is). Even for the rest, the problem usually lies in a subquery nested inside the problem update or insert.

This is akin to reducing an elaborate word problem in high-school
mathematics to a simple, abstract equation, where the solution of the
equation is generally almost automatic once you know the necessary
math. The abstract representation of a SQL tuning problem, the query
diagram, normally takes the form of an upside-down tree, with some
numbers attached, as shown in Figure 1-1.

Figure 1-1. An example of a query diagram

As it turns out, SQL is such a flexible language that it is capable
of producing queries that do not map to the usual tree form, but it
turns out that such queries almost never make sense from a business
perspective. This delivers an unplanned-for side benefit: in the
course of tuning SQL and producing the abstract query representations
that aid you in that process, certain problems with the logic of the
queries become obvious, even if you have no prior knowledge of the
application. Developers usually catch these problems before you see
the SQL, unless the problems lie in the sort of corner cases that
they might not test thoroughly, as these problems often do.

These corner-case problems can be the
worst kind for an applicationfor example, throwing accounts
out of balance long after the application goes live and is assumed to
be fine, in subtle ways that are hard to detect and hard to fix.

The worst of these problems will never be found. The business will simply operate based on wrong results, under-billing, over-billing, under-paying, over-paying, or otherwise just doing the wrong thing without anyone tying these problems to a correctable application bug.

Sometimes, fixing a
performance problem also requires you to fix a logic problem. Even
when the problems are independent (when you can fix performance
without fixing the logic flaw), you can perform a major added service
by identifying these logic flaws and seeing that they are fixed. This
book covers these logic flaws at length, including detailed
descriptions of how to find each one and what to do about it. I go so
far as to recommend you take any significantly complex, manually
written SQL through the SQL diagramming exercise just to find these
subtle logic errors, even if you already know that it performs well.
Depending on the tool, some products that autogenerate SQL avoid most
of these problems.