This Subject Is Important

"In our experience (confirmed by many industry experts) 80% of performance gains on SQL Server come from making improvements in SQL code, not from devising crafty configuration adjustments or tweaking the operating system."

Kevin Kline et al., Transact-SQL Programming, O'Reilly & Associates

"Experience shows that 80 to 90 per cent of all tuning is done at the application level, not at the database level."

Thomas Kyte, Expert One on One: Oracle, Wrox Press

No matter which DBMS you use, you can enhance its performance by doing the right thing. Enhancing performance is a broad field. It includes:

• Coding SQL statements without doing things that everyone knows are counter-productive

• Understanding the physical structure of a typical database

• Solving real problems rather than imaginary ones

Let's take a trivial example. Suppose you have this SQL statement:

 SELECT column1 FROM Table1   WHERE column1 = 77 

For this kind of statement, we believe the essential question isShould there be an index on column1 ? So we've devoted a whole chapter to the subject of indexeswhat they look like, what variations exist, how indexes affect data changes, and so on. In another chapter, we address the question of how to use EXPLAIN (or its equivalent) to find out whether your particular DBMS actually uses an index for a particular SELECT. That illustrates our prioritywe think that the first priority is the concept: indexes. Certainly, though, we must also care about the method: diagnostic tools. We hope that, with the concepts firmly planted in your mind, you will quickly arrive at the right point. We don't recommend that you implement any idea in this book without testing it firstbut without ideas, you'll flounder randomly between plans without knowing if your final choice really is the best one.

We think an idea is sound if performance improves by 5% or more on most DBMSs. That may appear to be a modest goal, but consider. First, we always test to ensure that the idea doesn't harm performance on some other DBMSwe believe an idea is only good when it applies universally . Second, we think that even a small number like 5% matters when an operation occurs many times for many rows. Third, we are asking you to read only once, because once you have the information, it's a tiny effort to reuse it for years . Fourth, the improvement often will be many times more than 5%. Fifth, effects may be small, but they're also cumulative.

We also hope that you find the topic, well, interesting. If it's any incentive at all, let us assure you that many database practitioners , and all the good ones, are fascinated by these two questionsHow does it work? How ought it to work?