Practical SQL: Tuning Queries
In This Chapter
- Defining the Basic Problem
- Understanding the Optimizer and Associated Tools
- Managing the WHERE Clause
- Creating Covering Indexes
- Joining Columns
- Sorting with DISTINCT and UNION
- Choosing Between HAVING and WHERE
- Looking at Views
- Forcing Indexes
Perform, #%&#@!
Can you influence database performance by the SQL you choose? Yes, you can. Mostly, this means knowing what indexes you have and how SQL code does or does not take advantage of them.
Of course, there are many other factors—database design, hardware configuration, system architecture, network setup—that have profound effects on performance. While it's true that a poorly written query can slow performance by invalidating relevant indexes, it's equally certain that a really well-crafted query cannot overcome fundamental system limitations.
Unfortunately, SQL tuning is never cut-and-dried. It's a constant weighing of one factor against another, requiring endless measuring and attention and frequent changes. In addition, you won't find the msdpn database much use in testing different approaches to queries. Most optimizers ignore indexes when the data set is very small and simply read the tables sequentially (do table scans). Review the queries in this section to make sure you understand the principles of writing efficient SQL, but be sure to test specifics on your system with something much closer to real data.
Understanding indexes and writing queries to take advantage of indexes can give you the best performance improvements on an existing database. These are the steps you go through when you start thinking about query performance:
Find out what kind of work your database is tuned for and what it looks like—the tables, views, and other objects in it. An entity-relationship diagram is very helpful. If you don't have one, use system tools to sketch out the major tables.
Get a list of indexes for the tables you'll be using via system catalogs, GUI interfaces, or commands.
Look into the details of your optimizer and identify tools for seeing how the optimizer handles different queries.
Armed with these tools, examine specific "slow" queries. First of all, make sure they are returning the correct results.
Review the WHERE clause. Are relevant indexes useful? Up to date? Is any construction nullifying an available index? Could you use a covering index?
Check joins—are join columns compatible?
Is there any unnecessary or redundant action? Are DISTINCT and UNION overused? Do you see HAVING used like WHERE? Are queries complicated by going through views?
There is a list of index/query areas to consider in the chapter summary.
Since index and optimizer tools vary greatly from system to system (they are not SQL commands), you'll find brief instructions on using the Adaptive Server Anywhere tools in this chapter—just enough to let you follow the examples if you are using the software on the CD. For details on Adaptive Server Anywhere (or basics on any other system), you're on your own!