Section 12.7. What Really Matters?


12.7. What Really Matters?

What really matters when trying to improve a query has been discussed in the previous chapters, namely:

  • The number of rows in the tables involved

  • The existing indexes on these tables

  • Storage peculiarities, such as partitioning , that can have as strong an impact as indexes on performance

  • The quality of the various criteria that were provided

  • The size of the resulting set

This information provides us with a solid foundation from which to investigate query performance, and is far more valuable than an execution plan on its own. Once we know were we stand, and what we have to fight against, then we can move, and attack tables, always trying to get rid of unwanted data as quickly as we can. We must always try to leave as much freedom to the optimizer as we can by avoiding any type of intra-statement dependencies that would constrain the order in which tables must be visited.

In conclusion, I would like to remind you that optimizers, which usually prove quite efficient at their job, are unable to work efficiently under the following circumstances:

  • If you retrieve data piecemeal through multiple statements. It is one thing for an application to issue a series of related SQL statements. However, the SQL engine can never "know" that such statements are related, and cannot optimize across statement boundaries. The SQL engine can optimize each individual statement, but it cannot optimize the overall process.

  • If you use, without any care, the numerous non-relational (and sometimes quite useful) features provided by the various SQL dialects.

Remember that you should apply non-relational features last, when the bulk of data retrieval is done (in the wider acceptance of retrieval; data must be retrieved before being updated or deleted). Non-relational features operate on finite sets (in other words, arrays), not on theoretically infinite relations.

There was a time when you could make a reputation as an SQL expert by identifying missing indexes and rewriting statements so as to remove functions that were applied to indexed columns. This time is, for the most part, gone. Most databases are over-indexed, although sometimes inadequately indexed. Functions applied to indexed columns are still encountered, but functional indexes provide a "quick fix" to that particular problem. However, rewriting a poorly performing query usually means more nowadays than shuffling conditions or merely making cosmetic changes.

The real challenge is more and more to be able to think globally, and to acknowledge that data handling is critical in a world where the amount of stored data increases even faster than the performance of the hardware. For better or for worse, data handling spells S-Q-L. Like all languages, SQL has its idiosyncrasies, its qualities, and numerous flaws. Like all languages, mastering SQL requires time, experienceand personal talent. I hope that on that long road this book will prove helpful to you.

Building optimally performing SQL can be a source of great satisfactionenjoy!




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net