One reason we believe expert human database tuners will always be in demand is that many of the biggest performance improvements come from viewing an application in a new light. Tools can, of course, help, but they would have to confront an enormous variety of situations, some of which we outline here.

Many of these examples come from our work on Wall Street, but we don't think the lessons are specific to that environment. For the sake of concreteness, however, we have left the context intact.

6.1 Techniques for Circumventing Superlinearity

As in many other applications, financial data often must be validated when it enters. This often happens in the context of a data warehouse, slet us study such a situation. You will see that small rewritings can yield big results (Figure 6.1).

Figure 6.1: Circumventing superlinearity. This graph compares the four techniques that we describe for circumventing superlinearity: (a) insertion followed by a check for deletions, (b) same as (a) with an index on the table used to check for deletions, (c) inserting sales and checking for deletions in small batches, and (d) using outer join. We use the unsuccessful sales example given in the text with two configurations of the data: small (500,000 sales, 400,000 items, 400,000 customers and 10,000 stores, and 400,000 successful sales) and large (1,000,000 sales, 800,000 items, same customers and stores tables as for the small workload, and around 800,000 successful sales). The experiment is performed using SQL Server 2000 on Windows 2000.