101 lines
7.9 KiB
HTML
101 lines
7.9 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="concept" />
|
||
|
|
<meta name="DC.Title" content="Performance and query optimization" />
|
||
|
|
<meta name="abstract" content="The goal of database performance tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance." />
|
||
|
|
<meta name="description" content="The goal of database performance tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="whatsnew.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqprint.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="queryoptimize.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqdta.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="per0001.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="queryopt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="efindex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="opens.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="progtech.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="genconsid.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqdmd.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqmrdm.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqmsgref.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rzajqkickoff" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Performance and query optimization</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqkickoff"><a name="rzajqkickoff"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Performance and query optimization</h1>
|
||
|
|
<div><p>The goal of database performance tuning is to minimize the response
|
||
|
|
time of your queries and to make the best use of your server's resources by
|
||
|
|
minimizing network traffic, disk I/O, and CPU time. This goal can only be
|
||
|
|
achieved by understanding the logical and physical structure of your data,
|
||
|
|
understanding the applications used on your server, and understanding how
|
||
|
|
the many conflicting uses of your database may impact database performance.</p>
|
||
|
|
<p>The best way to avoid performance problems is to ensure that performance
|
||
|
|
issues are part of your ongoing development activities. Many of the most significant
|
||
|
|
performance improvements are realized through careful design at the beginning
|
||
|
|
of the database development cycle. To most effectively optimize performance,
|
||
|
|
you must identify the areas that will yield the largest performance increases
|
||
|
|
over the widest variety of situations and focus your analysis on those areas. </p>
|
||
|
|
<p>Many of the examples within this publication illustrate a query written
|
||
|
|
through either an SQL or an OPNQRYF query interface. The interface chosen
|
||
|
|
for a particular example does not indicate an operation exclusive to that
|
||
|
|
query interface, unless explicitly noted. It is only an illustration of one
|
||
|
|
possible query interface. Most examples can be easily rewritten into whatever
|
||
|
|
query interface that you prefer.</p>
|
||
|
|
<div class="note"><span class="notetitle">Note:</span> Read the <a href="codedisclaimer.htm">Code license and disclaimer information</a> for important legal
|
||
|
|
information.</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="whatsnew.htm">What's new for V5R4</a></strong><br />
|
||
|
|
The following information was added or updated in this release of the information:</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqprint.htm">Printable PDF</a></strong><br />
|
||
|
|
Use this to view and print a PDF of this information.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="queryoptimize.htm">Query Engine Overview</a></strong><br />
|
||
|
|
DB2<sup>®</sup> UDB
|
||
|
|
for iSeries™ provides
|
||
|
|
two query engines to process queries: the Classic Query Engine (CQE) and the
|
||
|
|
SQL Query Engine (SQE). </li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqdta.htm">Data access on DB2 UDB for iSeries: data access paths and methods</a></strong><br />
|
||
|
|
Data access methods are used to process queries and access data.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="per0001.htm">Processing queries: Overview</a></strong><br />
|
||
|
|
This overview of the query optimizer provides guidelines for designing queries that will perform and will use server resources more efficiently.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="queryopt.htm">Optimizing query performance using query optimization tools</a></strong><br />
|
||
|
|
Query optimization is an iterative process. You can gather performance information about your queries and control the processing of your queries.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="efindex.htm">Creating an index strategy</a></strong><br />
|
||
|
|
<span class="keyword">DB2 Universal Database™ for iSeries™</span> provides
|
||
|
|
two basic means for accessing tables: a table scan and an index-based retrieval.
|
||
|
|
Index-based retrieval is typically more efficient than table scan when less
|
||
|
|
than 20% of the table rows are selected.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="opens.htm">Application design tips for database performance</a></strong><br />
|
||
|
|
There are some design tips that you can apply when designing SQL applications to maximize your database performance.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="progtech.htm">Programming techniques for database performance</a></strong><br />
|
||
|
|
By changing the coding of your queries, you can improve their performance.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="genconsid.htm">General DB2 UDB for iSeries performance considerations</a></strong><br />
|
||
|
|
As you code your applications, there are some general tips that can help you optimize performance.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqdmd.htm">Database monitor: Formats</a></strong><br />
|
||
|
|
This section contains the formats used to create the database monitor SQL tables and views.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqmrdm.htm">Memory Resident Database Monitor: DDS</a></strong><br />
|
||
|
|
The following DDS statements are used to create the memory resident database monitor physical and logical files.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqmsgref.htm">Query optimizer messages reference</a></strong><br />
|
||
|
|
See the following for query optimizer message reference:</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|