97 lines
6.0 KiB
HTML
97 lines
6.0 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="reference" />
|
||
|
|
<meta name="DC.Title" content="Redesign routines for performance" />
|
||
|
|
<meta name="abstract" content="Even following all of the implementation tips, sometimes a procedure or function may still not perform as well as it needs to. In that case, you need to look at the design of the procedure or UDF and see if there are any changes that can be made to improve the performance." />
|
||
|
|
<meta name="description" content="Even following all of the implementation tips, sometimes a procedure or function may still not perform as well as it needs to. In that case, you need to look at the design of the procedure or UDF and see if there are any changes that can be made to improve the performance." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafyudfperf.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="rbafyredesign" />
|
||
|
|
<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>Redesign routines for performance</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyredesign"><a name="rbafyredesign"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Redesign routines for performance</h1>
|
||
|
|
<div><p>Even following all of the implementation tips, sometimes a procedure
|
||
|
|
or function may still not perform as well as it needs to. In that case, you
|
||
|
|
need to look at the design of the procedure or UDF and see if there are any
|
||
|
|
changes that can be made to improve the performance. </p>
|
||
|
|
<div class="section"><p>There are two different types of design changes that you can look
|
||
|
|
at.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The first change is to reduce the number of database calls or
|
||
|
|
function calls that a procedure makes, a process similar to looking for blocks
|
||
|
|
of code that can be converted to SQL statements. Many times you can reduce
|
||
|
|
the number of calls by adding additional logic to your code.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>A more difficult design change is to restructure a whole function
|
||
|
|
to get the same result a different way. For example, your function uses a
|
||
|
|
SELECT statement to find a route that meets a particular set of criteria and
|
||
|
|
then executes that statement dynamically. By looking at the work that the
|
||
|
|
function is performing, you might be able to change the logic so that the
|
||
|
|
function can use a static SELECT query to find the answer, thereby improving
|
||
|
|
your performance.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>You should also use nested compound statements to localize exception
|
||
|
|
handling and cursors. If several specific handlers are specified, code is
|
||
|
|
generated to check to see if the error occurred after each statement. Code
|
||
|
|
is also generated to close cursors and process savepoints if an error occurs
|
||
|
|
in a compound statement. In routines with a single compound statement with
|
||
|
|
multiple handlers and multiple cursors, code is generated to process each
|
||
|
|
handler and cursor after every SQL statement. If you scope the handlers and
|
||
|
|
cursors to a nested compound statement, the handlers and cursors are only
|
||
|
|
checked within the nested compound statement.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In the following routine, code to check the SQLSTATE '22H11' error
|
||
|
|
will only be generated for the statements within the lab2 compound statement.
|
||
|
|
Specific checking for this error will not be done for any statements in the
|
||
|
|
routine outside of the lab2 block. Code to check the SQLEXCEPTION error will
|
||
|
|
be generated for all statements in both the lab1 and lab2 blocks. Likewise,
|
||
|
|
error handling for closing cursor c1 will be limited to the statements in
|
||
|
|
the lab2 block.</p>
|
||
|
|
<pre>Lab1: BEGIN
|
||
|
|
DECLARE var1 INT;
|
||
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
||
|
|
RETURN -3;
|
||
|
|
lab2: BEGIN
|
||
|
|
DECLARE EXIT HANDLER FOR SQLSTATE '22H11'
|
||
|
|
RETURN -1;
|
||
|
|
DECLARE c1 CURSOR FOR SELECT col1 FROM table1;
|
||
|
|
OPEN c1;
|
||
|
|
CLOSE c1;
|
||
|
|
END lab2;
|
||
|
|
END Lab1</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Because redesigning a whole routine takes a lot of effort, examine
|
||
|
|
routines that are showing up as key performance bottlenecks rather than looking
|
||
|
|
at the application as a whole. More important than redesigning existing performance
|
||
|
|
bottlenecks is to spend time during the design of the application thinking
|
||
|
|
about the performance impacts of the design. Focusing on areas of the application
|
||
|
|
that are expected to be high use areas and making sure that they are designed
|
||
|
|
with performance in mind saves you from having to do a redesign of those areas
|
||
|
|
later.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafyudfperf.htm" title="When creating stored procedures and user-defined functions (UDFs), the SQL procedural language processor on the iSeries does not always generate the most efficient code. However, you can do some changes to reduce the number of database engine calls needed and improve performance.">Improve performance of procedures and functions</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|