📄 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="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch14.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch16.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch16.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/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 database
designing and tuning. No matter how fine-tuned the database or how sound the database
structure, 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 language
of SQL and how to communicate with the database; now it's time to apply your knowledge
to real-life performance concerns. The objective of Day 15 is to recommend methods
for improving the performance of, or streamlining, an SQL statement. By the end of
today, 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 as
possible without being disqualified. The swimmers must have an acceptable technique,
be able to torpedo themselves through the water, and use all their physical resources
as effectively as possible. With each stroke and breath they take, competitive swimmers
remain <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 want
to accomplish and then strive to follow the path of least resistance. The more time
you spend planning, the less time you'll have to spend revising later. Your goal
should 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 a
tardy meal. Although you can write most queries in several ways, the arrangement
of 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 several
other factors when trying to improve general database performance, for example, concurrent
user transactions that occur within a database, indexing of tables, and deep-down
database 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 important
if you have multiple conditions in the <TT>WHERE</TT> clause. Anyone reading the
clause should be able to determine whether the tables are being joined properly and
should 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 to
read. It is very <I>structured; </I>that is, the logical components of the query
have been separated by carriage returns and consistent spacing. You can quickly see
what 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 table
in order to execute an SQL statement. Full-table scans are normally an issue when
dealing with queries or the <TT>SELECT</TT> statement. However, a full-table scan
can also come into play when dealing with updates and deletes. A full-table scan
occurs when the columns in the <TT>WHERE</TT> clause do not have an index associated
with them. A full-table scan is like reading a book from cover to cover, trying to
find 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 used
as conditions in the <TT>WHERE</TT> clause of an SQL statement. Indexes provide a
direct path to the data the same way an index in a book refers the reader to a page
number. 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 server
would have to refer to the index, read the table, refer to the index again, read
the table again, and so on. On the other hand, indexes are most efficient when the
data 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 consider
table size when you are designing tables and indexes. Properly indexing tables involves
familiarity with the data, knowing which columns will be referenced most, and may
require 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 unreasonable
amount 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 added
to a front-end application. One of the first things to do when you begin to troubleshoot
is to find out whether the target table has an index. In most of the cases we have
seen, 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 two
or more columns in a table. These indexes can be more efficient than single-column
indexes 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 as
together, 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 suits
your database.
<H2><FONT COLOR="#000077"><B>Arrangement of Elements in a Query</B></FONT></H2>
<P>The best arrangement of elements within your query, particularly in the <TT>WHERE</TT>
clause, really depends on the order of the processing steps in a specific implementation.
The arrangement of conditions depends on the columns that are indexed, as well as
on which condition will retrieve the fewest records.</P>
<P>You do not have to use a column that is indexed in the <TT>WHERE</TT> clause,
but it is obviously more beneficial to do so. Try to narrow down the results of the
SQL statement by using an index that returns the fewest number of rows. The condition
that returns the fewest records in a table is said to be the <I>most restrictive
condition</I>. As a general statement, you should place the most restrictive conditions
last in the <TT>WHERE</TT> clause. (Oracle's query optimizer reads a <TT>WHERE</TT>
clause from the bottom up, so in a sense, you would be placing the most restrictive
condition first.)</P>
<P>When the optimizer reads the most restrictive condition first, it is able to narrow
down the first set of results before proceeding to the next condition. The next condition,
instead of looking at the whole table, should look at the subset that was selected
by the most selective condition. Ultimately, data is retrieved faster. The most selective
condition may be unclear in complex queries with multiple conditions, subqueries,
calculations, and several combinations of the <TT>AND</TT>, <TT>OR</TT>, and <TT>LIKE</TT>.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Always check your database documentation
to see how SQL statements are processed in your implementation.
<HR>
</BLOCKQUOTE>
<P>The following test is one of many we have run to measure the difference of elapsed
time between two uniquely arranged queries with the same content. These examples
use Oracle7.3 relational database management system. Remember, the optimizer in this
implementation reads the <TT>WHERE</TT> clause from the bottom up.</P>
<P>Before creating the <TT>SELECT</TT> statement, we selected distinct row counts
on each condition that we planned to use. Here are the values selected for each condition:</P>
<P>
<TABLE BORDER="1">
<TR>
<TD VALIGN="TOP"><I>Condition</I></TD>
<TD VALIGN="TOP"><I>Distinct Values</I></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>calc_ytd = '-2109490.8'</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP">13,000 +</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>dt_stmp = '01-SEP-96'</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP">15</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>output_cd = '001'</TT></TD>
<TD ALIGN="LEFT" VALIGN="TOP">13</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT" VALIGN="TOP"><TT>activity_cd = 'IN'</TT></TD>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -