Trang 1
SQL Performance Tuning By Peter Gulutzan, Trudy Pelzer Publisher: Addison Wesley Pub Date: September 10, 2002 ISBN: 0-201-79169-2 Pages: 528 SQL Performance Tuning is a handbook of practical solutions for busy database professionals charged with managing an organization's critically important data. Covering today's most popular and widely installed database environments, this book is an indispensable resource for managing and tuning SQL across multiple platforms. Packed with tips, techniques, and best practices, this hands-on guide covers SQL commands and queries over a wide range of conditions and environments. Every facet of the SQL environment is covered, from concurrency control to optimization—and every issue in between. The book includes an overview of each of the most widely used database management systems (DBMS) and provides tuning tips for common day-to-day situations. Topics covered include: • General and specific tuning • General sort considerations and options • Joins and subqueries • Columns and tables • Indexes, including B-trees and bitmaps • Constraints and stored procedures • ODBC and JDBC • Data changes and concurrency control (locking) • Clients, servers, and middleware Whether you are a programmer, consultant, or user, you will find SQL Performance Tuning an indispensable guide to optimizing SQL performance across today's popular database environments. Throughout SQL Performance Tuning, ideas and techniques are tested on a variety of popular SQL environments, including IBM DB2, Informix, Ingres, InterBase, Microsoft SQL Server, MySQL, Oracle, and Sybase ASE. The book's practical examples are written in ANSI/ISO Standard SQL: 1999. Copyright Preface You Don't Know that Your DBMS Is Good … You Know that Even Good Tools Work Better in Skilled Hands … Acknowledgments Chapter 1. Facilis Descensus Averni
Trang 2
This Subject Is Important The Big Eight Test Results Portability Terminology and Expectations Generalities Chapter 2. Simple Searches General Tuning Specific Tuning Style Notes Parting Shots Chapter 3. ORDER BY General Sort Considerations Character Sorts Other Options Parting Shots Chapter 4. GROUP BY Refresher Optimal GROUP BY Clauses Sorting Set Functions and Summary Aggregates Parting Shots Chapter 5. Joins Join Plan Strategies Avoid the Join Strategies Three-Way Joins and Beyond Old Style versus ANSI Style Parting Shots Chapter 6. Subqueries Refresher Join versus Subquery Syntax Choices Parting Shots Chapter 7. Columns How Big Is the Size Factor? Characters Temporals Numbers Bits Large Objects NULLs Column Order Within Rows
Trang 3
Parting Shots Chapter 8. Tables The Storage Hierarchy Heaps Clusters The Normal Forms Views Parting Shots Chapter 9. Indexes Refresher B-trees Types of Indexes Bitmap Indexes Other Index Variations Index Key Values Parting Shots Chapter 10. Constraints NOT NULL CHECK FOREIGN KEY PRIMARY KEY UNIQUE Triggers Disabling Constraints Client Validations Redundant SELECT Clauses Parting Shots Chapter 11. Stored Procedures Refresher Advantages of Stored Procedures Parting Shots Chapter 12. ODBC Refresher SQLPrepare Fetch Loops Data-Change Statements Catalog Functions Parting Shots Chapter 13. JDBC Connections Query Prepping Result Sets
Trang 4
Data Changes Parting Shots Chapter 14. Data Changes Logs INSERT UPDATE DELETE Ugly Updates FETCH and Data Changes COMMIT and ROLLBACK Parting Shots Chapter 15. Locks What Is a Lock? Isolation Levels Index Locks Hot Spots Optimistic Locking The Scan Trick Parting Shots Chapter 16. Clients and Servers Middleware Server Processes and Threads What Should the Client Do? Parting Shots Chapter 17. Cost-Based Optimizers Cost Statistics and Histograms EXPLAIN Hints Parting Shots Appendix A. Further Reading Appendix B. Glossary Copyright Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and Addison-Wesley, Inc. was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals. The authors and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is
Trang 5
assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein. The publisher offers discounts on this book when ordered in quantity for bulk purchases and special sales. For more information, please contact: U.S. Corporate and Government Sales (800) 382-3419 corpsales@pearsontechgroup.com For sales outside of the U.S., please contact: International Sales (317) 581-3793 international@pearsontechgroup.com Visit A-W on the Web: www.awprofessional.com Library of Congress Cataloging-in-Publication Data Gulutzan, Peter. SQL performance tuning / Peter Gulutzan and Trudy Pelzer. p. m. Includes bibliographical references and index. ISBN 0-201-79169-2 (alk. paper) 1. SQL (Computer program language) 2. Relational databases. I. Pelzer, Trudy. II. Title. QA76.73.S67 G853 2003 005.75'6—dc21 2002074418 Copyright © 2003 Pearson Education, Inc All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form, or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior consent of the publisher. Printed in the United States of America. Published simultaneously in Canada.
Trang 6
For information on obtaining permission for use of material from this work, please submit a written request to: Pearson Education, Inc. Rights and Contracts Department 75 Arlington Street, Suite 300 Boston, MA 02116 Fax: (617) 848-7047 Text printed on recycled paper 1 2 3 4 5 6 7 8 9 10—CRS—0605040302 First printing, September 2002 Preface A poorly performing database application can cost each user time and have an impact on other applications running on the same computer or the same network. The purpose of this book is to help you improve the performance of your SQL database. It is not an introduction to SQL syntax, not a tuning manual for a specific SQL implementation, and not a guide to design or data modelling. Instead, we've written it for users and programmers who want to improve SQL performance, no matter what brand of SQL they use. In this book, you'll find tuning tips for common situations, such as, "How to change a query so it will go faster," "What an index does," and "Shifting work from the server to the client." Rather than exploiting the unique features of a single DBMS, we're going to give you ideas that are good for all of the major SQL DBMSs. Client/server programmers and consultants need to appreciate what can happen when the DBMS changes, or—the most challenging situation—the DBMS is unknown. So we tested all the ideas in this book on eight well-known DBMSs. A good DBMS already contains a good optimizer. Yet you have picked up a book that promises to help you do your own tuning. That means that either you don't know something … or you do know something. • You don't know that your DBMS is good. • You know that even good tools work better in skilled hands. You Don't Know that Your DBMS Is Good … That could be true if you're a student or new on the job. That could be true especially if you're writing queries or programs that should work on more than one DBMS. You're most likely to encounter one of the following RDBMSs with the largest market shares (based on new license sales figures for the year 2001)[1]:
Trang 7
[1] Data from Gartner Dataquest (May 2002). Oracle 39.8% IBM 30.7% (prior to acquisition of Informix) Microsoft 14.4% Sybase 3.3% Informix 3.3% You might also run into DBMSs that are popular for Web work (like MySQL), for work with Borland products (like InterBase), for desktops (like Access), for mobile and Java interfacing (like Cloudscape), or for embedded systems (like Solid), or a host of small fry, like mSQL and gadfly. This book tries to be useful for the common denominator in all products. To use automotive analogies, it's not a "mechanic's guide to tuning the 1999 Cadillac" book, it's a "driver's guide to optimizing performance of modern cars" book—even if you have a manual transmission. You Know that Even Good Tools Work Better in Skilled Hands … Everybody has heard of sluggish SQL queries, or even whole systems, that a heroic someone improved with small effort. Usually the improvement is small too, so we will avoid extravagant promises. But we will make you the following guarantees. • You will be able to follow our arguments without deep thinking or hard work. All we assume is that you have basic knowledge of programming and standard SQL syntax. In fact, you can read this book on a plane or at the beach. • All of our observations have been confirmed by tests on real DBMSs within the last several months. • We know that "optimizing" is very different from "over-clocking," and we only discuss safe, rational, relational techniques. One accusation that could be leveled, and to which we plead guilty, is that some of our material is ad hoc advice instead of general principles. Of course! There are only a few general principles in DBMS optimization. • First, do no harm. (Actually that's from Hippocrates' ancient manual on medical treatments. It applies to anybody fixing what ain't really broke.) • Get more memory, add indexes, re-cable the network. (If you can influence the environment like that, then do so.) • Design right in the first place. • Understand the query. Instead of general principals, we will be looking at what can be done with what's likely to be at hand. If we descend sometimes to mere tips and warnings about traps, that's because we've seen over the years that examples of real situations can help people realize what the general rules are. As has often been observed, tips should be based on principles. The DBMSs that we looked at while preparing this book include IBM DB2, Informix, Ingres II, InterBase, Microsoft SQL Server, MySQL, Oracle, and Sybase (MS Windows NT versions). Each was installed and tested using the default switches recommended in the vendors' instructions. To avoid
Trang 8
favoring any vendor's idiosyncrasies, all SQL examples in this book are written in ANSI/ISO Standard SQL:1999. Host-language examples are written in C plus ODBC, or Java plus JDBC. Your DBMS is your pal. We won't counsel you to subvert it, be paranoid about it, or insult it by assuming it's stupid. Rather, as you would with a pal, you should try to get to know it better, and help it to help you. Acknowledgments Several people took the time to look at draft manuscripts of this book, and we would like to pass on our grateful acknowledgments to them, without suggesting that they share any responsibility for our book's flaws or views. To Judy Bowman, author of Practical SQL: The Sequel, for insights about Sybase and an eye for jarring phrases. To Keith Hare, member of the ANSI SQL standards committee, for identifying several technical mistakes. To Dan Hotka and Chuck Kosin, for their helpful and encouraging comments. To Craig Mullins, author of DB2 Developer's Guide, for explaining as only he can what IBM DB2 really does. To Alexander Tarasul, who gave us the viewpoint of a veteran who has consulted for many database projects. To Mika Wendelius, Oracle whiz, for taking the time and patience to show us in person some surprises that only a true Oracle expert can know. And to Martti Laiho, Senior Lecturer in Data Management at Helsinki Business Polytechnic, not only for his long hours doing multiple reviews, but for giving us a chance to test our ideas in front of a live audience. Chapter 1. Facilis Descensus Averni[1] [1] Literally, "the descent into Hell is easy" . . . it's getting back out again that's hard! From Virgil. When we decided to write this book, we chose the topic "Tuning Client/Server SQL Programs" because we had a specific plan. Each word in the topic narrows the focus. Let's look at the main words. Tuning means enhancing speed. It's possible to make a distinction between tuning and optimizing— tuning is what you do to a database (e.g., change cache size, repartition, rebuild indexes) while optimizing is what you do to a program (e.g., adjust queries, take advantage of existing resources, rewrite application code). But we do not make such a fine distinction in this book. As for speed, the word can mean two things: response time (the time a statement takes to execute) and throughput (the number of operations the DBMS can do in a time unit). Our concern here is mostly with throughput.
Trang 9
SQL is a language that is supported by a Database Management System (DBMS) implementation. We decided to use a generic term so you'll know that we're not talking about a particular brand—this book is about what's in common for all major brands. There really is a standard core that they all share, so we can be specific in our recommendations without having to be specialists. Where there are many differences between DBMSs, you'll see charts outlining the differences. Where there are few differences, you'll see a discussion of what's normal and (if necessary) a sidebar about the exceptional case. Programs are things you write in a language such as SQL, C, or Java. So don't expect to see long discussions about how to choose equipment, what flags to set when you install, or which tools you'll need to monitor the users. Those tasks properly belong to the Database Administrator (DBA). We know that much of this book will interest DBAs; we know that many readers are both DBAs and programmers. That's great. We only want to make it clear that, in this book, we are addressing the people who write applications. 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 is—Should there be an index on column1? So we've devoted a whole chapter to the subject of indexes—what 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 priority—we 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 first—but without ideas, you'll flounder randomly between plans without knowing if your final choice really is the best one.
Trang 10
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 DBMS—we 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 questions—How does it work? How ought it to work? The Big Eight "In theory there is no difference between theory and practice. But, in practice, there is." —Jan L.A. van de Snepscheut You don't have to specialize to find useful ideas in this book. Over and over again, we have found that basic matters that are true for DBMS #1 are also true for DBMS #2, DBMS #3, and so on, across the board. For example, we can say that "DBMSs store data in fixed-size pages and the size is a power of two." But isn't that a bold generalization? Let's be honest. We have not checked this statement on every DBMS that exists, we know it's not a law of nature, and in fact we know of at least two DBMSs for which the statement is false. But, in this book, when we make a claim for "all DBMSs" or just "DBMSs" we're not being vague or general—we mean, very specifically, eight particular DBMSs that we have actually tested and for which we guarantee that the statement is true at time of writing. We call these DBMSs "the Big Eight"—not a standard term, but a convenient way to direct your memory to this introductory explanation. We chose the Big Eight according to the following criteria: • The DBMS must be an SQL client/server DBMS. DBMSs of other types were excluded. • The DBMS must have at least a 1% share of the market in North America and Europe according to published and verifiable surveys, or it must be widely known because it's open source. • The DBMS must support Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC). We want to emphasize that no DBMS got on our list due to its quality. We chose each of the Big Eight based only on the probability that you'll encounter it or something very much like it. Because the Big Eight have a combined market share of over 85%, with the open source DBMSs having been downloaded hundreds of thousands of times from Internet sites, we're confident that you'll be dealing with one of the Big Eight at least 90% of the time. Table 1-1 shows which DBMSs were tested for this book. When researching the material for this book, we installed the latest versions of each DBMS available for the MS WindowsNT platform at the time of writing. Each DBMS was installed and tested using the default systems recommended in the vendors' instructions, except as indicated in the following section, "Installation Parameters." In some cases, we used evaluation copies or personal editions; in no case did we test with extra-cost or little-used options—our policy was to ignore nondefault installation options, settings, and switches.
Trang 11
Table 1-1. The Big Eight Short Product Name & Version Remarks Name IBM IBM DB2 Universal Database 7.2 Informix IBM Informix Dynamic Now owned by IBM Server 9.3 Ingres Ingres II 2.5 Owned by Computer Associates International InterBase InterBase 6.0 Open source version. Owned by Borland Software Corporation Microsoft Microsoft SQL Server 2000 MySQL MySQL 3.23 Open source. Owned by MySQL AB Oracle Oracle 9i Sybase Sybase ASE 12.5 We have avoided endorsing or denouncing any vendor, because our object is to help you improve your SQL, given the hand you've been dealt. There are test results throughout this book; however, there are no absolute performance figures or inter-DBMS comparisons. There are two reasons for this. One is obvious: such figures wouldn't fit the purpose of the book. The other is interesting: three of the Big Eight—Informix, Microsoft, and Oracle—have end-user license agreements that specifically prohibit publication of benchmarks. Installation Parameters As indicated earlier, in order to minimize our use of extra-cost or little-used options and to level the playing field between DBMSs as much as possible, we installed the Big Eight using the default systems recommended in the vendors' instructions except in the following cases: • For the sake of consistency, we wanted to run all our tests using the same character set—the Windows 1252 code page—for every DBMS if at all possible. We chose this code page because we were testing on a Windows NT system and wanted to utilize international character set possibilities. • We wanted to test the differences between dictionary and binary sorts for every DBMS if at all possible. For IBM, these criteria meant that the IBM database was created with the default "IBM-1252" character set and default "Local Alphabet" dictionary sort order. We used CHAR columns to test dictionary sorts and CHAR FOR BIT DATA columns to test binary sorts. IBM doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options. For Informix, these criteria meant that Informix was installed with the default "EN_US 8859-1" for client and server locales, and nondefault "EN_GB.8859-1" db_locale, which provides a dictionary sort order for NCHAR columns. We used NCHAR columns to test dictionary sorts and CHAR columns to test binary sorts. Informix doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options. For Ingres, these criteria meant that the Ingres database was created with the default "WIN1252" character set and the nondefault "lmulti" dictionary sort order. We used CHAR columns to test
Trang 12
dictionary sorts and BYTE columns to test binary sorts. Ingres doesn't provide SQL Standard- compliant CHARACTER SET or COLLATE options. For InterBase, these criteria meant that the InterBase database was created with DEFAULT CHARACTER SET WIN1252. We used NCHAR columns with COLLATE EN_US to test dictionary sorts and NCHAR columns with no COLLATE clause to test binary sorts. We also used NCHAR columns with (a) COLLATE DA_DA to test Danish/Norwegian sorts, (b) COLLATE DE_DE to test German sorts, (c) COLLATE IS_IS to test Icelandic sorts, (d) COLLATE EN_UK to test Irish sorts, (e) COLLATE ES_ES to test Spanish sorts, and (f) COLLATE FI_FI and COLLATE SV_SV to test Swedish/Finnish sorts. For Microsoft, these criteria meant that SQL Server was installed with the default "1252/ISO" Character Set and the default "Dictionary Order, case insensitive" Sort Order for CHAR columns, and a nondefault "Binary" Unicode Collation. We used CHAR columns with no COLLATE clause to test dictionary sorts and CHAR columns with COLLATE SQL_Latin1_General_BIN to test binary sorts. We also used CHAR columns with (a) COLLATE SQL_Danish to test Danish/Norwegian sorts, (b) COLLATE German_PhoneBook to test German phone book sorts, (c) COLLATE SQL_Icelandic to test Icelandic sorts, (d) COLLATE Mexican_Trad_Spanish and COLLATE Modern_Spanish to test Spanish sorts, and (e) COLLATE SQL_SwedishStd to test Swedish/Finnish sorts. (Note: Where applicable, PREF was always indicated, the code page was 1252, case sensitivity was CI, and accent sensitivity was AS.) For MySQL, these criteria meant that MySQL was installed with the default "Latin1" (aka iso_1) character set. We used CHAR columns to test dictionary sorts and CHAR BINARY columns to test binary sorts. MySQL doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options. For Oracle, these criteria meant that Oracle was installed with the default "WIN1252" character set. We used CHAR columns with NLS_SORT=XWEST_EUROPEAN to test dictionary sorts and CHAR columns with NLS_SORT=BINARY to test binary sorts. We also used CHAR columns with (a) NLS_SORT=DANISH and NLS_SORT=NORWEGIAN to test Danish/Norwegian sorts, (b) NLS_SORT=XGERMAN to test German dictionary sorts, (c) NLS_SORT=GERMAN_DIN to test German phone book sorts, (d) NLS_SORT=ICELANDIC to test Icelandic sorts, (e) NLS_SORT=XWEST_EUROPEAN to test Irish sorts, (f) NLS_SORT=XSPANISH to test Spanish Traditional sorts, (g) NLS_SORT=SPANISH to test Spanish Modern sorts, and (h) NLS_SORT=FINNISH to test Swedish/Finnish sorts. For Sybase, these criteria meant that Sybase was installed with the nondefault "Character Set = iso_1" and nondefault "Sort Order = Dictionary." We used CHAR columns to test dictionary sorts and BINARY columns to test binary sorts. Sybase doesn't provide SQL Standard-compliant CHARACTER SET or COLLATE options. Test Results Throughout this book, you'll see sets of SQL statements that show a test we ran against the Big Eight. The second statement in each example includes a note at the bottom that states: GAIN: x/8. That's an important number. The gain shows how many of the Big Eight run faster when an SQL statement is optimized by implementing the syntax shown in the second example. We recorded a gain for a DBMS
Trang 13
if performance improved by 5% or greater. Mileage varies with different data and different machines, of course. We're only reporting what our tests showed. "GAIN: 0/8" means "you'd be wasting your time if you rearranged this particular SQL statement into optimum order because the DBMS does this for you." "GAIN: 4/8," on the other hand, means that half of the Big Eight performed better when the suggested syntax was used, while the other half executed both statements equally well. Even if the gain is only 1/8 (meaning only one of the Big Eight improved on the second statement), you'll be better off using our suggested syntax because this means you'll improve performance some of the time without ever harming performance the rest of the time. That is, none of our suggestions will cause a performance decline on any of the Big Eight—with one exception. The exception is that, in a few cases, one DBMS showed aberrant behavior and declined in performance on the second statement, while all the rest showed impressive gains. In such cases, we felt the possible improvement was worthy of mention anyway. Each exception notes the DBMS with which you should not use our improved syntax. All tests were run on a single CPU Windows NT machine, with no other jobs running at the same time. The main test program was written in C and used ODBC calls to communicate with the DBMSs. A second test program used JDBC to test the DBMSs' response to specific calls. Unless otherwise indicated, each performance test was run three times for 10,000 rows of randomly inserted data, with the relevant column(s) indexed as well as without indexes. The gain for each DBMS was then calculated as the average of the three test runs. We want to emphasize that our gain figures do not show absolute performance benchmark results. That is, a "GAIN: 4/8" note does not mean that any or all DBMSs ran 50% faster. It merely means that 50% of the DBMSs ran faster, and the rest showed no change. Portability As a bit of a bonus, because we had access to all these DBMSs, we will also be able to give you some information about portability. We regard portability as a matter of great importance. In the first place, this book is about client/server applications. We anticipate that you will want to write code that works regardless of DBMS. In fact, we anticipate that you may not even know for which DBMS you are coding! To avoid depending on any vendor's idiosyncrasies, all SQL examples and descriptions of SQL syntax in this book are written in standard SQL—that is, ANSI/ISO SQL:1999—whenever possible. When standard SQL omits a feature but it's common to all DBMSs—for example, the CREATE INDEX statement—our examples use syntax that will run on most platforms. Where nonstandard and uncommon syntax exists or had to be tested, we have identified it as such. Look for our "Portability" notes; they indicate where syntax other than standard SQL might be required. To aid you, we've also added comparison charts that highlight differences between the SQL Standard and the Big Eight. In these tables, you'll sometimes see "N/S" in the ANSI SQL row. This means that the SQL Standard considers the feature to be implementation-defined; that is, there is no Standard- specified requirement. Instead, the decision on how to implement the feature is made by the DBMS vendor.
Trang 14
Optimizing SQL for all dialects is different from tuning for a single package. But the coverage in this book is strictly the universal stuff. Terminology and Expectations We expect that you're a programmer coding for (or about to begin coding for) an SQL DBMS. Because of this, we won't be explaining basic SQL syntax or programming techniques. Our assumption is that you already know basic SQL syntax, how to program with an SQL Application Programming Interface (API) such as ODBC or JDBC, how to write stored procedures, are familiar with how indexes operate, and so on. We also expect that you're familiar with SQL terms as used in introductory SQL texts. For example, suppose we illustrate a SELECT statement like this: SELECT <select list> WHERE <search conditions> FROM <Table list> GROUP BY <grouping columns> HAVING <conditions> ORDER BY <sorting columns> We assume you've already seen terms like "select list" and "search condition" and "grouping column," and we won't repeat their well-known definitions. At most, we'll just provide a brief refresher for common SQL syntax and concepts. Some further terms are not so well known, but are important for understanding this book. We will define such terms the first time we use them. If you miss such a definition, you can look it up in the glossary in Appendix B. Conventions We use a particular style in our examples. SQL keywords are always in uppercase (e.g., SELECT). Table and other major SQL object names are initial capitalized (e.g., Table1, Index1); column names are in lowercase (e.g., column1). When it is necessary to use more than one line, each line will begin with a clause-leader keyword. We deliberately avoid "real-world" names like Employees or cust_id because we believe that meaningful names would distract from the universality of the example. Sometimes, though, when illustrating a particular characteristic, we will use a name that hints at the item's nature. For example: SELECT column1, column2 FROM Table1 WHERE indexed_column = <literal> This book doesn't contain many SQL syntax diagrams, but here's a very brief refresher on the common variant of Backus-Naur Form (BNF) notation that we've used: • < > Angle brackets surround the names of syntactic elements. Replace the names with real data.
Trang 15
• [ ] Square brackets surround optional syntax. You may either use or omit such syntax. • { } Braces surround mandatory syntax groups. You must include one of the options for the group in your SQL statement. • | The vertical bar separates syntactic elements. You may use only one of the options in your SQL statement. Generalities "Beware of entrance to a quarrel; but being in, Bear't that th'opposed may beware of thee." —William Shakespeare, Hamlet We can start off with some general tips. SQL is a procedural language. Despite the confusion and outright lies about this point, it is a fact that an SQL statement's clauses are processed in a fixed order. And despite the set orientation of SQL, the DBMS must often operate on an SQL result set one row at a time. That is, if you're executing this statement: UPDATE Table1 SET column1 = 5 WHERE column2 > 400 SQL's set orientation means the DBMS will determine that, for example, six rows meet the condition that requires them to be updated. After that, though, the DBMS must actually change all six of the rows—one row at a time. The relational model is inherently efficient. Dr. Codd's rules and the subsequent work on normalization are based on the proven truism that mathematical foundations lead to solid structures. (Normalization is the process of designing a database so that its tables follow the rules specified by relational theory.) Always assume you will do a query at least 100 times. That will make you ask yourself whether you want to use a procedure, a view, a trigger, or some other object and might also make you ask—If this is so common, has someone already written something like it? And now, let's get started. Chapter 2. Simple Searches
Trang 16
In this chapter, we'll talk about syntax-based optimizing and simple search conditions. A syntax is a choice of words and their arrangement in the SQL statement. To optimize based on syntax, assume that nonsyntactic factors (e.g., indexes, table sizes, storage) are irrelevant or unknown. This is the lowest level of optimizing—it's usually predictable, and some of it can be done on the client. There's no point in attempting to optimize most SQL syntax because only certain SQL statements have options that lend themselves to optimization. The particular syntax that offers many optimization possibilities is the SQL search condition. Here are three examples of search conditions: ... WHERE title LIKE 'The %' OR title LIKE 'A %' ... WHERE name <> 'Smith' ... WHERE number = 5 Although the slowest search conditions are those that contain joins and subqueries, this chapter deals only with single-table searches. (We'll talk about joins and subqueries later.) Also, although search conditions can appear in HAVING, IF, or ON clauses, we'll talk only about search conditions in WHERE clauses. So the chapter title—"Simple Searches"—is an understatement. Don't worry—the complex queries will come later. General Tuning In this part of the chapter, we'll look at some general ideas you should keep in mind when writing simple search conditions. Code for Points The best search conditions are those that work on few rows with easy-to-do comparisons. Table 2-1 and Table 2-2 show typical lists (derived from vendors' manuals) of types of search conditions, in order from best to worst. Each search condition component has a "point count"—the better the component, the higher the score. You can see from the allotted points shown in Tables 2-1 and 2-2 that the best search condition is something like: ... WHERE smallint_column = 12345 Table 2-1. Search Condition Point Counts for Operators Operator Points = 10 > 5 >= 5 < 5 <= 5 LIKE 3 <> 0 Table 2-2. Search Condition Point Counts for Operands Operand Points
Trang 17
Literal alone 10 Column alone 5 Parameter alone 5 Multioperand expression 3 Exact numeric data type 2 Other numeric data type 1 Temporal data type 1 Character data type 0 NULL 0 This example gets a total of 27 points, calculated as follows: • Five points for the column (smallint_column) alone on the left • Two points for the exact numeric (smallint_column) operand data type • Ten points for the equals operator • Ten points for the literal (12345) alone on the right Here's another example: ... WHERE char_column >= varchar_column || 'x' The point count for this type of search condition is much lower—only 13: • Five points for the column (char_column) alone on the left • Zero points for the CHAR (char_column) operand data type • Five points for the greater than or equal to operator • Three points for the multioperand expression (varchar_column || 'x') on the right • Zero points for the VARCHAR (varchar_column) operand data type The precise point count for a search condition varies from vendor to vendor, so it's pointless to memorize anything other than the order and the concept for this optimization technique. So just remember: The condition that takes the least time—usually because it involves fewer rows or easier comparisons—gets the most points. Armed with this concept, you can decide whether to change the order of expressions, or to substitute one expression for another that does the same work. Even though a modern cost-based DBMS optimizer has many more rules that require information outside the SQL statement itself, all DBMSs still fall back on the point count when no other information is available. The possibility is always there that you will use an item of information that the optimizer doesn't. (For more information about cost- based optimizers, see Chapter 17 "Cost-Based Optimizers.") Another way you can optimize a search condition is by putting multiple expressions in the correct order. The expressions in this WHERE clause are already in optimal order:
Trang 18
SELECT * FROM Table1 WHERE column1 = 5 AND column2 = 77.3 AND column3 = 'Smith' AND column4 < 117 AND column4 > column5 GAIN: 0/8 The note at the bottom of this example says there is a GAIN: 0/8. That's an important number, and we're going to say "GAIN: x/8" in many of our examples, so let's clarify. As explained in Chapter 1, the gain shows how many of the Big Eight run faster when the search condition is optimized. Mileage varies with different data and different machines, of course. We're only reporting what our tests showed. So "GAIN: 0/8" means "you'd be wasting your time if you rearranged this particular WHERE clause into optimum order because the DBMS does this for you." All DBMS makers know the basics of point counting, so the rearrangement is automatic. This means that, in ordinary cases, you will gain nothing by doing your own syntax-based optimization. However, there are many exceptions to the rule. For the rest of this chapter, we'll look at cases where the gain is both significant and predictable. Constant Propagation "All men are mortal. Socrates is a man. Therefore, Socrates is mortal." —Attributed to Aristotle Formally, the Law of Transitivity states that: IF (A <comparison operator> B) IS TRUE AND (B <comparison operator> C) IS TRUE THEN (A <comparison operator> C) IS TRUE AND NOT (A <comparison operator> C) IS FALSE (Comparison operator is any one of: = or > or >= or < or <= but not one of: <> or LIKE.) The Law of Transitivity leads to the simple observation that we can substitute C for B without changing the meaning of an expression. When such a substitution involves substituting a constant value, the process is called constant propagation.[1] [1] The Law of Transitivity, like many arithmetic rules, uses two-valued logic. That's unsafe if NULLs are involved, so it's a lucky thing that SQL never returns true when any operand is NULL. And—betcha didn't know—the SQL Standard definition of a literal is "a constant that is not NULL." So constant propagation is a misleading term—it would be better to call it "literal propagation." The next two expressions mean the same thing, but the second expression has a better point count because it substitutes a literal (5) for a column name (column1): Expression #1
Trang 19
... WHERE column1 < column2 AND column2 = column3 AND column1 = 5 Expression #2 ... WHERE 5 < column2 AND column2 = column3 AND column1 = 5 GAIN: 2/8 Expression #2 is called a transform of Expression #1. (Writing a transform means rewriting an SQL statement to produce the same result but with different syntax. When two SQL statements have different syntax, but will predictably and regularly produce the same outputs, they are known as transforms of one another.) Most good DBMSs do this sort of thing automatically. But some DBMSs won't try transforms when the expression contains multiple parentheses and NOTs. For example, this SELECT statement can be slow: SELECT * FROM Table1 WHERE column1 = 5 AND NOT (column3 = 7 OR column1 = column2) Applying the transforms ourselves, we came up with this statement: SELECT * FROM Table1 WHERE column1 = 5 AND column3 <> 7 AND column2 <> 5 GAIN: 5/8 The transformed statement is faster more than half of the time. In other words, sometimes it pays to do your own transforms. Sometimes constant propagation won't work with floats, because it's possible to be both "greater than" and "equal to" at the same time when approximate numeric comparisons happen. When it does work, though, expect a GAIN: 5/8. And sometimes, constant propagation won't work for CHAR expressions. But when it does, expect a GAIN: 4/8. Exercise time: The MySQL online documentation has this example: ... WHERE a < b AND b = c AND a = 5 transforms to: ... WHERE b > 5 AND b = c AND a = 5 The quiz question here is—Did the MySQL folks make a mistake?[2] [2] Answer to the quiz question: No. The MySQL people were correct.
Trang 20
In the real world, you'll find many semiconstant operands, as program parameters or functions. Examples are the niladic functions like CURRENT_DATE (a niladic function is a function that has no arguments). Because using a constant value always accelerates accesses, try a transform to speed up these cases. Here's an example: Query #1 transforms to Query #2: Query #1: SELECT * FROM Table1 WHERE date_column = CURRENT_DATE AND amount * 5 > 100.00 Query #2: SELECT * FROM Table1 WHERE date_column = DATE '2002-01-01' AND amount * 5 > 100.00 GAIN: 5/8 If you're thinking of transforming this type of expression, keep in mind that (because of the DATE constant), you'd have to change the query every day. That's only practical when an application program is generating the queries on the server. Dead Code Elimination "Branches with no leaves should be cut off." —Ortho Guide To Pruning Bushes and Shrubs In some old SQL programs, you'll encounter literals on both sides of the comparison operator, as in this example: SELECT * FROM Table1 WHERE 0 = 1 AND column1 = 'I hope we never execute this' In the days before C-style /* comments */ were legal in an SQL statement, this was a way to add an inline comment string. Because the expression 0 = 1 is always false, this query will always return zero rows, and therefore DBMSs can skip the whole WHERE clause. But some of them don't. We tested this by removing the WHERE clause and got a gain: SELECT * FROM Table1 GAIN: 5/8 It is, of course, obvious that these two queries aren't equivalent—the point is merely that it should take less time to retrieve zero rows due to an always-false condition than it should to do a full table scan-provided the DBMS doesn't evaluate the always-false condition. This example shows that DBMSs don't always throw out always-false conditions and all their dependents in the PREPARE stage. But they're pretty reliable at throwing out always-true conditions. So you can use always- true conditions for an SQL equivalent of conditional compilation. For example, if you worry that a DBMS won't give high precision for division results, add a separate condition that comes into play only when necessary—as in this example:
Hiển thị 20 trang đầu. Tải tài liệu để xem đầy đủ.
Bình luận (0)
Chưa có bình luận nào. Hãy là người đầu tiên!