Chapter 16 -- Query Tuning

Chapter 16

If you want to end up with a poorly performing application or a complete project failure, you should wait until the end of the project to deal with performance concerns. If, however, you want your application to be the best it can be, you must consider performance throughout the development cycle. In fact, you must consider performance before you even write your first line of code.

Every chapter of this book has included some information on performance. If you've turned directly to this chapter in hopes of finding the secret to improving your lackluster application, you'll be disappointed. I can offer only guidelines for you to keep in mind, along with some pointers that refer back to earlier information or to other helpful materials.

Microsoft SQL Server systems can be brilliantly fast with well-designed, well-implemented applications. It can support workloads of the type and size that no one dreamed possible back in 1988 when SQL Server first became available. But with a poorly planned or poorly implemented system, SQL Server can perform horribly. Statements like "SQL Server is slow" are not uncommon. Neither are such statements about other database products. Anytime you hear this from someone who has deployed a production application, your first thought should be that the person has dropped the ball somewhere along the line—or that SQL Server is unsuitable for the task at hand. (SQL Server can handle most systems, but some are still beyond its reach.)

If this is the first chapter you've turned to, please stop and go back at least to Chapter 3. All of the chapters from Chapter 3 to this one are relevant to performance issues. You might also revisit Chapter 13, which covers cursors, and Chapter 14, which covers locking. A thorough understanding of cursors and locking is a prerequisite for understanding the material in this chapter.

The big gains in query performance usually do not come from syntactic changes in the query but rather from a change in the database design or in indexing—or from taking a completely different approach to the query. For example, you might have to choose among the following approaches: writing a pretty complex query using a self-join or multilevel correlated subquery, using a cursor, or creating a solution that involves temporary tables. (You saw some of these techniques in Chapter 10.) Invariably, the only way you can determine which solution is best is to try all the queries. You might be surprised by the results.

Rather than trying to learn a bunch of tricks to do up front, you should be much more interested in doing the basics right. That is, you need to be sure that you've set up a good database structure—including perhaps some denormalization based on your CRUD analysis (described below)—and that you've created in advance what appear to be useful indexes. From there, you can test your queries and study the query plans generated for any queries that seem problematic.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

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