SQL Tuning

Chapter 10. Outside-the-Box Solutions to Seemingly Unsolvable Problems

It is not every question that deserves an answer.

—Publilius Syrus (a.k.a. Publius) Maxim 581

Computers are fast enough that they never have to be the bottleneck in a business process. When they turn out to be a bottleneck, solutions always exist. However, the solutions do not always take the form of answers to the question “How do I make this query return the same rows faster?” So far, this book has focused on answering that single question, and Chapter 9 described a few circumstances in which satisfactory answers to that question do not exist. This chapter takes the performance problem out of that self-imposed box and considers how to solve the rare query runtime problems that cannot be solved just by tuning a given statement.

When Very Fast Is Not Fast Enough

Online steps in a business process that run in less than a second are unlikely to significantly slow the end user who is performing the process. Even steps that take well over a second can often be made convenient to the end user if those parts of the process are taken offline and made into batch processes. The only business-driven need for queries to run very fast—under a half a second, for example—comes about when a single step in a business process requires queries to run repeatedly. Some application designs repeat a query hundreds of times for a single online event, or up to millions of times for a single batch process. In these cases, clearly, a ...