📄 ch15.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 15 -- Streamlining SQL Statements for Improved Performance</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../ch14/ch14.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch16/ch16.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- 15 - <BR>Streamlining SQL Statements for Improved Performance</FONT></H1></CENTER><P>Streamlining SQL statements is as much a part of application performance as databasedesigning and tuning. No matter how fine-tuned the database or how sound the databasestructure, you will not receive timely query results that are acceptable to you,or even worse, the customer, if you don't follow some basic guidelines. Trust us,if the customer is not satisfied, then you can bet your boss won't be satisfied either.<H2><FONT COLOR="#000077"><B>Objectives</B></FONT></H2><P>You already know about the major components of the relational database languageof SQL and how to communicate with the database; now it's time to apply your knowledgeto real-life performance concerns. The objective of Day 15 is to recommend methodsfor improving the performance of, or streamlining, an SQL statement. By the end oftoday, you should<UL> <LI>Understand the concept of streamlining your SQL code<BR> <BR> <LI>Understand the differences between batch loads and transactional processing and their effects on database performance<BR> <BR> <LI>Be able to manipulate the conditions in your query to expedite data retrieval<BR> <BR> <LI>Be familiar with some underlying elements that affect the tuning of the entire database</UL><P>Here's an analogy to help you understand the phrase <I>streamline an SQL statement</I>:The objective of competitive swimmers is to complete an event in as little time aspossible without being disqualified. The swimmers must have an acceptable technique,be able to torpedo themselves through the water, and use all their physical resourcesas effectively as possible. With each stroke and breath they take, competitive swimmersremain <I>streamlined</I> and move through the water with very little resistance.</P><P>Look at your SQL query the same way. You should always know exactly what you wantto accomplish and then strive to follow the path of least resistance. The more timeyou spend planning, the less time you'll have to spend revising later. Your goalshould always be to retrieve accurate data and to do so in as little time as possible.An end user waiting on a slow query is like a hungry diner impatiently awaiting atardy meal. Although you can write most queries in several ways, the arrangementof the components within the query is the factor that makes the difference of seconds,minutes, and sometimes hours when you execute the query. <I>Streamlining SQL</I>is the process of finding the optimal arrangement of the elements within your query.</P><P>In addition to streamlining your SQL statement, you should also consider severalother factors when trying to improve general database performance, for example, concurrentuser transactions that occur within a database, indexing of tables, and deep-downdatabase tuning.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Today's examples use Personal Oracle7 and tools that are available with the Oracle7.3 relational database management system. The concepts discussed today are not restricted to Oracle; they may be applied to other relational database management systems. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077"><B>Make Your SQL Statements Readable</B></FONT></H2><P>Even though readability doesn't affect the actual performance of SQL statements,good programming practice calls for readable code. Readability is especially importantif you have multiple conditions in the <TT>WHERE</TT> clause. Anyone reading theclause should be able to determine whether the tables are being joined properly andshould be able to understand the order of the conditions.</P><P>Try to read this statement:</P><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT EMPLOYEE_TBL.EMPLOYEE_ID, EMPLOYEE_TBL.NAME,EMPLOYEE_PAY_TBL.SALARY,EMPLOYEE_PAY_TBL.HIRE_DATE</B> 2 <B>FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL</B> 3 <B>WHERE EMPLOYEE_TBL.EMPLOYEE_ID = EMPLOYEE_PAY_TBL.EMPLOYEE_ID AND</B> 4 <B>EMPLOYEE_PAY_TBL.SALARY > 30000 OR (EMPLOYEE_PAY_TBL.SALARY BETWEEN 25000</B> 5 <B>AND 30000 AND EMPLOYEE_PAY_TBL.HIRE_DATE < SYSDATE - 365);</B></FONT></PRE><P>Here's the same query reformatted to enhance readability:</P><PRE><FONT COLOR="#0066FF">SQL> <B>SELECT E.EMPLOYEE_ID, E.NAME, P.SALARY, P.HIRE_DATE</B> 2 <B>FROM EMPLOYEE_TBL E,</B> 3 <B> EMPLOYEE_PAY_TBL P</B> 4 <B>WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID</B> 5 <B> AND P.SALARY > 30000</B> 6 <B> OR (P.SALARY BETWEEN 25000 AND 30000</B> 7 <B> AND P.HIRE_DATE < SYSDATE - 365);</B></FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the use of table aliases in the preceding query. <TT>EMPLOYEE_TBL</TT> in line 2 has been assigned the alias <TT>E</TT>, and <TT>EMPLOYEE_PAY_TBL</TT> in line 3 has been assigned the alias <TT>P</TT>. You can see that in lines 4, 5, 6, and 7, the <TT>E</TT> and <TT>P</TT> stand for the full table names. Aliases require much less typing than spelling out the full table name, and even more important, queries that use aliases are more organized and easier to read than queries that are cluttered with unnecessarily long full table names. <HR></BLOCKQUOTE><P>The two queries are identical, but the second one is obviously much easier toread. It is very <I>structured; </I>that is, the logical components of the queryhave been separated by carriage returns and consistent spacing. You can quickly seewhat is being selected (the <TT>SELECT </TT>clause), what tables are being accessed(the <TT>FROM</TT> clause), and what conditions need to be met (the <TT>WHERE</TT>clause).<H2><FONT COLOR="#000077"><B>The Full-Table Scan</B></FONT></H2><P>A full-table scan occurs when the database server reads every record in a tablein order to execute an SQL statement. Full-table scans are normally an issue whendealing with queries or the <TT>SELECT</TT> statement. However, a full-table scancan also come into play when dealing with updates and deletes. A full-table scanoccurs when the columns in the <TT>WHERE</TT> clause do not have an index associatedwith them. A full-table scan is like reading a book from cover to cover, trying tofind a keyword. Most often, you will opt to use the index.</P><P>You can avoid a full-table scan by creating an index on columns that are usedas conditions in the <TT>WHERE</TT> clause of an SQL statement. Indexes provide adirect path to the data the same way an index in a book refers the reader to a pagenumber. Adding an index speeds up data access.</P><P>Although programmers usually frown upon full-table scans, they are sometimes appropriate.For example:<UL> <LI>You are selecting most of the rows from a table.<BR> <BR> <LI>You are updating every row in a table.<BR> <BR> <LI>The tables are small.</UL><P>In the first two cases an index would be inefficient because the database serverwould have to refer to the index, read the table, refer to the index again, readthe table again, and so on. On the other hand, indexes are most efficient when thedata you are accessing is a small percentage, usually no more than 10 to 15 percent,of the total data contained within the table.</P><P>In addition, indexes are best used on large tables. You should always considertable size when you are designing tables and indexes. Properly indexing tables involvesfamiliarity with the data, knowing which columns will be referenced most, and mayrequire experimentation to see which indexes work best.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When speaking of a "large table," <I>large</I> is a relative term. A table that is extremely large to one individual may be minute to another. The size of a table is relative to the size of other tables in the database, to the disk space available, to the number of disks available, and simple common sense. Obviously, a 2GB table is large, whereas a 16KB table is small. In a database environment where the average table size is 100MB, a 500MB table may be considered massive. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077"><B>Adding a New Index</B></FONT></H2><P>You will often find situations in which an SQL statement is running for an unreasonableamount of time, although the performance of other statements seems to be acceptable;for example, when conditions for data retrieval change or when table structures change.</P><P>We have also seen this type of slowdown when a new screen or window has been addedto a front-end application. One of the first things to do when you begin to troubleshootis to find out whether the target table has an index. In most of the cases we haveseen, the target table has an index, but one of the new conditions in the <TT>WHERE</TT>clause may lack an index. Looking at the <TT>WHERE</TT> clause of the SQL statement,we have asked, Should we add another index? The answer may be yes if:<UL> <LI>The most restrictive condition(s) returns less than 10 percent of the rows in a table.<BR> <BR> <LI>The most restrictive condition(s) will be used often in an SQL statement.<BR> <BR> <LI>Condition(s) on columns with an index will return unique values.<BR> <BR> <LI>Columns are often referenced in the <TT>ORDER BY</TT> and <TT>GROUP BY</TT> clauses.</UL><P>Composite indexes may also be used. A <I>composite index</I> is an index on twoor more columns in a table. These indexes can be more efficient than single-columnindexes if the indexed columns are often used together as conditions in the <TT>WHERE</TT>clause of an SQL statement. If the indexed columns are used separately as well astogether, especially in other queries, single-column indexes may be more appropriate.Use your judgment and run tests on your data to see which type of index best suitsyour database.<H2><FONT COLOR="#000077"><B>Arrangement of Elements in a Query</B></FONT></H2>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -