ch15.htm

21精通SQL

is to have a separate disk for each major database entity, including large tables
and indexes.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The DBA and system administrator
	should work together to balance database space allocation and optimize the memory
	that is available on the server. 
<HR>


</BLOCKQUOTE>

<P>Tuning a database very much depends on the specific database system you are using.
Obviously, tuning a database entails much more than just preparing queries and letting
them fly. On the other hand, you won't get much reward for tuning a database when
the application SQL is not fine-tuned itself. Professionals who tune databases for
a living often specialize on one database product and learn as much as they possibly
can about its features and idiosyncrasies. Although database tuning is often looked
upon as a painful task, it can provide very lucrative employment for the people who
truly understand it.
<H2><FONT COLOR="#000077"><B>Performance Obstacles</B></FONT></H2>
<P>We have already mentioned some of the countless possible pitfalls that can hinder
the general performance of a database. These are typically general bottlenecks that
involve system-level maintenance, database maintenance, and management of SQL statement
processing.</P>
<P>This section summarizes the most common obstacles in system performance and database
response time.

<UL>
	<LI>Not making use of available devices on the server--A company purchases multiple
	disk drives for a reason. If you do not use them accordingly by spreading apart the
	vital database components, you are limiting the performance capabilities. Maximizing
	the use of system resources is just as important as maximizing the use of the database
	server capabilities.<BR>
	<BR>
	
	<LI>Not performing frequent <TT>COMMIT</TT>s--Failing to use periodic <TT>COMMIT</TT>s
	or <TT>ROLLBACK</TT>s during heavy batch loads will ultimately result in database
	bottlenecks.<BR>
	<BR>
	
	<LI>Allowing batch loads to interfere with daily processing--Running batch loads
	during times when the database is expected to be available will cause problems for
	everybody. The batch process will be in a perpetual battle with end users for system
	resources.<BR>
	<BR>
	
	<LI>Being careless when creating SQL statements--Carelessly creating complex SQL
	statements will more than likely contribute to substandard response time.
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>You can use various methods to optimize
	the structure of an SQL statement, depending upon the steps taken by the database
	server during SQL statement processing. 
<HR>


</BLOCKQUOTE>


<UL>
	<LI>Running batch loads with table indexes--You could end up with a batch load that
	runs all day and all night, as opposed to a batch load that finishes within a few
	hours. Indexes slow down batch loads that are accessing a high percentage of the
	rows in a table.<BR>
	<BR>
	
	<LI>Having too many concurrent users for allocated memory--As the number of concurrent
	database and system users grows, you may need to allocate more memory for the shared
	process. See your system administrator.<BR>
	<BR>
	
	<LI>Creating indexes on columns with few unique values--Indexing on a column such
	as <TT>GENDER</TT>, which has only two unique values, is not very efficient. Instead,
	try to index columns that will return a low percentage of rows in a query.<BR>
	<BR>
	
	<LI>Creating indexes on small tables--By the time the index is referenced and the
	data read, a full-table scan could have been accomplished.<BR>
	<BR>
	
	<LI>Not managing system resources efficiently--Poor management of system resources
	can result from wasted space during database initialization, table creation, uncontrolled
	fragmentation, and irregular system/database maintenance.<BR>
	<BR>
	
	<LI>Not sizing tables and indexes properly--Poor estimates for tables and indexes
	that grow tremendously in a large database environment can lead to serious fragmentation
	problems, which if not tended to, will snowball into more serious problems.
</UL>

<H2><FONT COLOR="#000077"><B>Built-In Tuning Tools</B></FONT></H2>
<P>Check with your DBA or database vendor to determine what tools are available to
you for performance measuring and tuning. You can use performance-tuning tools to
identify deficiencies in the data access path; in addition, these tools can sometimes
suggest changes to improve the performance of a particular SQL statement.</P>
<P>Oracle has two popular tools for managing SQL statement performance. These tools
are <TT>explain</TT> <TT>plan</TT> and <TT>tkprof</TT>. The <TT>explain</TT> <TT>plan</TT>
tool identifies the access path that will be taken when the SQL statement is executed.
<TT>tkprof</TT> measures the performance by time elapsed during each phase of SQL
statement processing. Oracle Corporation also provides other tools that help with
SQL statement and database analysis, but the two mentioned here are the most popular.
If you want to simply measure the elapsed time of a query in Oracle, you can use
the SQL*Plus command <TT>SET TIMING ON</TT>.</P>
<P><TT>SET TIMING ON</TT> and other <TT>SET</TT> commands are covered in more depth
on Day 20, &quot;SQL*Plus.&quot;</P>
<P>Sybase's SQL Server has diagnostic tools for SQL statements. These options are
in the form of <TT>SET</TT> commands that you can add to your SQL statements. (These
commands are similar to Oracle's <TT>SET</TT> commands). Some common commands are
<TT>SET SHOWPLAN ON</TT>, <TT>SET STATISTIC IO ON</TT>, and <TT>SET STATISTICS TIME
ON</TT>. These <TT>SET</TT> commands display output concerning the steps performed
in a query, the number of reads and writes required to perform the query, and general
statement-parsing information. SQL Server <TT>SET</TT> commands are covered on Day
19, &quot;Transact-SQL: An Introduction.&quot;
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Two major elements of streamlining, or tuning, directly affect the performance
of SQL statements: application tuning and database tuning. Each has its own role,
but one cannot be optimally tuned without the other. The first step toward success
is for the technical team and system engineers to work together to balance resources
and take full advantage of the database features that aid in improving performance.
Many of these features are built into the database software provided by the vendor.</P>
<P>Application developers must know the data. The key to an optimal database design
is thorough knowledge of the application's data. Developers and production programmers
must know when to use indexes, when to add another index, and when to allow batch
jobs to run. Always plan batch loads and keep batch processing separate from daily
transactional processing.</P>
<P>Databases can be tuned to improve the performance of individual applications that
access them. Database administrators must be concerned with the daily operation and
performance of the database. In addition to the meticulous tuning that occurs behind
the scenes, the DBA can usually offer creative suggestions for accessing data more
efficiently, such as manipulating indexes or reconstructing an SQL statement. The
DBA should also be familiar with the tools that are readily available with the database
software to measure performance and provide suggestions for statement tweaking.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q If I streamline my SQL statement, how much of a gain in performance should
	I expect?</B><BR>
	<BR>
	<B>A </B>Performance gain depends on the size of your tables, whether or not columns
	in the table are indexed, and other relative factors. In a very large database, a
	complex query that runs for hours can sometimes be cut to minutes. In the case of
	transactional processing, streamlining an SQL statement can save important seconds
	for the end user.<BR>
	<BR>
	<B>Q How do I coordinate my batch loads or updates?</B><BR>
	<BR>
	<B>A </B>Check with the database administrator and, of course, with management when
	scheduling a batch load or update. If you are a system engineer, you probably will
	not know everything that is going on within the database.<BR>
	<BR>
	<B>Q How often should I commit my batch transactions?</B><BR>
	<BR>
	<B>A </B>Check with the DBA for advice. The DBA will need to know approximately how
	much data you are inserting, updating, or deleting. The frequency of <TT>COMMIT</TT>
	statements should also take into account other batch loads occurring simultaneously
	with other database activities.<BR>
	<BR>
	<B>Q Should I stripe all of my tables?</B><BR>
	<BR>
	<B>A </B>Striping offers performance benefits only for large tables and/or for tables
	that are heavily accessed on a regular basis.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What does <I>streamline an SQL statement </I>mean?<BR>
	<BR>
	<B>2. </B>Should tables and their corresponding indexes reside on the same disk?<BR>
	<BR>
	<B>3. </B>Why is the arrangement of conditions in an SQL statement important?<BR>
	<BR>
	<B>4. </B>What happens during a full-table scan?<BR>
	<BR>
	<B>5. </B>How can you avoid a full-table scan?<BR>
	<BR>
	<B>6. </B>What are some common hindrances of general performance?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1. </B>Make the following SQL statement more readable.
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>SELECT EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_NAME,
EMPLOYEE.ADDRESS, EMPLOYEE.PHONE_NUMBER, PAYROLL.SALARY, PAYROLL.POSITION,
EMPLOYEE.SSN, PAYROLL.START_DATE FROM EMPLOYEE, PAYROLL WHERE
EMPLOYEE.SSN = PAYROLL.SSN AND EMPLOYEE.LAST_NAME LIKE 'S%' AND
PAYROLL.SALARY &gt; 20000;</B></FONT></PRE>

</BLOCKQUOTE>

<PRE><FONT COLOR="#0066FF"><B></B></FONT></PRE>

<DL>
	<DD><B>2. </B>Rearrange the conditions in the following query to optimize data retrieval
	time. Use the following statistics (on the tables in their entirety) to determine
	the order of the conditions:
</DL>



<BLOCKQUOTE>
	<P>593 individuals have the last name <TT>SMITH</TT>.</P>
	<P>712 individuals live in <TT>INDIANAPOLIS</TT>.</P>
	<P>3,492 individuals are <TT>MALE</TT>.</P>
	<P>1,233 individuals earn a salary &gt;= <TT>30,000</TT>.</P>
	<P>5,009 individuals are single.

</BLOCKQUOTE>


<DL>
	<DD><TT>Individual_id</TT> is the primary key for both tables.
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>SELECT M.INDIVIDUAL_NAME, M.ADDRESS, M.CITY, M.STATE, M.ZIP_CODE,
       S.SEX, S.MARITAL_STATUS, S.SALARY
FROM MAILING_TBL M,
     INDIVIDUAL_STAT_TBL S
WHERE M.NAME LIKE 'SMITH%'
  AND M.CITY = 'INDIANAPOLIS'
  AND S.SEX = 'MALE'
  AND S.SALARY &gt;= 30000
  AND S.MARITAL_STATUS = 'S'
  AND M.INDIVIDUAL_ID = S.INDIVIDUAL_ID;</B>
--------------</FONT></PRE>

</BLOCKQUOTE>

<CENTER>
<P>
<HR>
<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><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>