66 lines
5.6 KiB
HTML
66 lines
5.6 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="Programming techniques for database performance" />
|
||
|
|
<meta name="abstract" content="By changing the coding of your queries, you can improve their performance." />
|
||
|
|
<meta name="description" content="By changing the coding of your queries, you can improve their performance." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqkickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="impopt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="fetchnrows.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="insertnrows.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="ipbc.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="selectopt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="ipps.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="refreshfwd.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="progtech" />
|
||
|
|
<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>Programming techniques for database performance</title>
|
||
|
|
</head>
|
||
|
|
<body id="progtech"><a name="progtech"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Programming techniques for database performance</h1>
|
||
|
|
<div><p>By changing the coding of your queries, you can improve their performance.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="impopt.htm">Use the OPTIMIZE clause</a></strong><br />
|
||
|
|
If an application is not going to retrieve the entire result table for a cursor, using the OPTIMIZE clause can improve performance. The query optimizer modifies the cost estimates to retrieve the subset of rows using the value specified on the OPTIMIZE clause.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="fetchnrows.htm">Use FETCH FOR n ROWS</a></strong><br />
|
||
|
|
Applications that perform many FETCH statements in succession may be improved by using FETCH FOR n ROWS. With this clause, you can retrieve multiple rows of data from a table and put them into a host structure array or row storage area with a single FETCH.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="insertnrows.htm">Use INSERT n ROWS</a></strong><br />
|
||
|
|
Applications that perform many INSERT statements in succession may be improved by using INSERT n ROWS. With this clause, you can insert one or more rows of data from a host structure array into a target table. This array must be an array of structures where the elements of the structure correspond to columns in the target table.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="ipbc.htm">Control database manager blocking</a></strong><br />
|
||
|
|
To improve performance, the SQL runtime attempts to retrieve and insert rows from the database manager a block at a time whenever possible.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="selectopt.htm">Optimize the number of columns that are selected with SELECT statements</a></strong><br />
|
||
|
|
The number of columns that you specify in the select list of a SELECT statement causes the database manager to retrieve the data from the underlying tables and map the data into host variables in the application programs. By minimizing the number of columns that are specified, processing unit resource usage can be conserved.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="ipps.htm">Eliminate redundant validation with SQL PREPARE statements</a></strong><br />
|
||
|
|
The processing which occurs when an SQL PREPARE statement is run is similar to the processing which occurs during precompile processing.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="refreshfwd.htm">Page interactively displayed data with REFRESH(*FORWARD)</a></strong><br />
|
||
|
|
In large tables, paging performance is typically degraded because
|
||
|
|
of the REFRESH(*ALWAYS) parameter on the <span class="cmdname">Start SQL (STRSQL) </span>command
|
||
|
|
which dynamically retrieves the latest data directly from the table. Paging
|
||
|
|
performance can be improved by specifying REFRESH(*FORWARD).</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqkickoff.htm" title="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.">Performance and query optimization</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|