165 lines
11 KiB
HTML
165 lines
11 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="Build your own connection pooling" />
|
||
|
|
<meta name="abstract" content="You can develop your own connection and statement pooling without requiring support for DataSources or relying on another product." />
|
||
|
|
<meta name="description" content="You can develop your own connection and statement pooling without requiring support for DataSources or relying on another product." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="jdbcpool.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="poolwdts.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="poolprop.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="poolstat.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="poolnods" />
|
||
|
|
<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>Build your own connection pooling</title>
|
||
|
|
</head>
|
||
|
|
<body id="poolnods"><a name="poolnods"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Build your own connection pooling</h1>
|
||
|
|
<div><p>You can develop your own connection and statement pooling without
|
||
|
|
requiring support for DataSources or relying on another product.</p>
|
||
|
|
<p>The pooling techniques are demonstrated on a small Java™ application,
|
||
|
|
but are equally applicable to servlets or large n-tiered applications. This
|
||
|
|
example is used to demonstrate the performance issues.</p>
|
||
|
|
<p>The demonstration application has two functions:</p>
|
||
|
|
<ul><li>To insert a new index and name into a database table.</li>
|
||
|
|
<li>To read the name for a given index from the table.</li>
|
||
|
|
</ul>
|
||
|
|
<p>The complete code to a connection pooling application can be downloaded
|
||
|
|
from </p>
|
||
|
|
<p><a href="http://www.ibm.com/developerworks/wireless/library/wi-tip28.html" target="_blank">JDBC tips and trick</a>.</p>
|
||
|
|
<p>The example application does not perform well. Running 100 calls to the
|
||
|
|
getValue method and 100 calls to the putValue method through this code took
|
||
|
|
an average of 31.86 seconds on a standard workstation.</p>
|
||
|
|
<p>The problem is that there is too much database work for every request.
|
||
|
|
That is, you get a connection, get a statement, process the statement, close
|
||
|
|
the statement, and close the connection. Instead of discarding everything
|
||
|
|
after each request, there must be a way to reuse portions of this process. <strong>Connection
|
||
|
|
pooling</strong> is replacing the create connection code with code to obtain a
|
||
|
|
connection from the pool, and then replacing the close connection code with
|
||
|
|
code to return the connection to the pool for use.</p>
|
||
|
|
<p>The connection pool's constructor creates the connections and places them
|
||
|
|
in the pool. The pool class has take and put methods for locating a connection
|
||
|
|
to use and for returning the connection to the pool when done working with
|
||
|
|
the connection. These methods are synchronized because the pool object is
|
||
|
|
a shared resource, but you do not want multiple threads to simultaneously
|
||
|
|
try to manipulate the pooled resources.</p>
|
||
|
|
<p>There is a change to the calling code for the getValue method. The putValue
|
||
|
|
method is not shown, but the exact change is made to it and is available from
|
||
|
|
IBM's Developer Kit for Java JDBC Web page. The instantiation of
|
||
|
|
the connection pool object is also not shown. You can call the constructor
|
||
|
|
and pass in the number of connection objects that you want in the pool. This
|
||
|
|
step should be done when you start up the application.</p>
|
||
|
|
<p>Running the previous application (that is, having 100 getValue method and
|
||
|
|
100 putValue method requests) with these changes took an average of 13.43
|
||
|
|
seconds with the connection pooling code in place. The processing time for
|
||
|
|
the workload is cut by more than half the original processing time without
|
||
|
|
connection pooling.</p>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Build your own statement pooling</h4><p> When using connection
|
||
|
|
pooling, time is wasted when creating and closing a statement when each statement
|
||
|
|
is processed. This is another example of wasting an object that can be reused.
|
||
|
|
</p>
|
||
|
|
<p>To reuse an object, you can use the prepared statement class. In most
|
||
|
|
applications, the same SQL statements are reused with minor changes. For example,
|
||
|
|
one iteration through an application might generate the following query:</p>
|
||
|
|
<blockquote><pre>SELECT * from employee where salary > 100000</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>The next iteration might generate the following query:</p>
|
||
|
|
<blockquote><pre>SELECT * from employee where salary > 50000</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>This is the same query, but it uses a different parameter. Both queries can
|
||
|
|
be accomplished with the following query:</p>
|
||
|
|
<blockquote><pre>SELECT * from employee where salary > ?</pre>
|
||
|
|
</blockquote>
|
||
|
|
<p>You
|
||
|
|
can then set the parameter marker (denoted by the question mark) to 100000
|
||
|
|
when processing the first query and 50000 when processing the second query.
|
||
|
|
This enhances performance for three reasons beyond what the connection pool
|
||
|
|
can offer:</p>
|
||
|
|
<ul><li>Fewer objects are created. A PreparedStatement object is created and reused
|
||
|
|
instead of creating a Statement object for every request. Therefore, you run
|
||
|
|
fewer constructors.</li>
|
||
|
|
<li>The database work to set up the SQL statement (called the <strong>prepare</strong>)
|
||
|
|
can be reused. Preparing SQL statements is reasonably expensive as it involves
|
||
|
|
determining what the SQL statement text says and how the system should accomplish
|
||
|
|
the task requested.</li>
|
||
|
|
<li>When removing the additional object creations, there is a benefit that
|
||
|
|
is not often considered. There is no need to destroy what was not created.
|
||
|
|
This model is easier on the Java garbage collector and also benefits
|
||
|
|
performance over time with many users.</li>
|
||
|
|
</ul>
|
||
|
|
<p>The demonstration program can be changed to pool PreparedStatement
|
||
|
|
objects instead of Connections. Changing the program allows you to reuse more
|
||
|
|
object and improve performance. You can begin by writing the class that contains
|
||
|
|
the objects to be pooled. This class must encapsulate the various resources
|
||
|
|
to be used. For the connection pool example, the Connection was the only pooled
|
||
|
|
resource, so there was no need for an encapsulating class. Each pooled object
|
||
|
|
must contain a Connection and two PreparedStatements. You can then create
|
||
|
|
a pool class that contains database access objects instead of connections.</p>
|
||
|
|
<p>Finally,
|
||
|
|
the application must change to obtain a database access object and specify
|
||
|
|
which resource from the object it wants to use. Other than specifying the
|
||
|
|
specific resource, the application remains the same.</p>
|
||
|
|
<p>With this change,
|
||
|
|
the same test run now takes an average of 0.83 seconds. This time is about
|
||
|
|
38 times faster than the original version of the program.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><h4 class="sectiontitle">Considerations</h4><p>Performance improves through replication.
|
||
|
|
If an item is not reused, then it is wasting resources to pool it.</p>
|
||
|
|
<p>Most
|
||
|
|
applications contain critical sections of code. Typically, an application
|
||
|
|
uses 80 to 90 percent of its processing time on only 10 to 20 percent of the
|
||
|
|
code. If there are 10,000 SQL statements potentially used in an application,
|
||
|
|
not all of them are pooled. The objective is to identify and pool the SQL
|
||
|
|
statements that are used in the application's critical sections of code.</p>
|
||
|
|
<p>Creating
|
||
|
|
objects in a Java implementation can carry a heavy cost. The pooling
|
||
|
|
solution can be used with advantage. Objects used in the process are created
|
||
|
|
at the beginning, before other users attempt to use the system. These objects
|
||
|
|
are reused as often as required. Performance is excellent and it is possible
|
||
|
|
to fine-tune the application over time to facilitate its use for greater numbers
|
||
|
|
of users. As a result, more objects are pooled. Moreover, it permits more
|
||
|
|
efficient multithreading of the application's database access to gain greater
|
||
|
|
throughput.</p>
|
||
|
|
<p>Java (using JDBC) is based on dynamic SQL and tends
|
||
|
|
to be slow. Pooling can minimize this problem. By preparing the statements
|
||
|
|
at startup, access to the database can be rendered static. There is little
|
||
|
|
difference in performance between dynamic and static SQL after the statement
|
||
|
|
is prepared.</p>
|
||
|
|
<p>The performance of database access in Java can
|
||
|
|
be efficient and can be accomplished without sacrificing object-oriented design
|
||
|
|
or code maintainability. Writing code to build statement and connection pooling
|
||
|
|
is not difficult. Furthermore, the code can be changed and enhanced to support
|
||
|
|
multiple applications and application types (Web-based, client/server) and
|
||
|
|
so on.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="jdbcpool.htm" title="Object pooling is the most common topic to come up when discussing Java Database Connectivity (JDBC) and performance. Since many objects used in JDBC are expensive to create such as Connection, Statement, and ResultSet objects, significant performance benefits can be achieved by reusing these objects instead of creating every time you need them.">JDBC object pooling</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="poolwdts.htm" title="You can use DataSources to have multiple applications share a common configuration for accessing a database. This is accomplished by having each application reference the same DataSource name.">Use DataSource support for object pooling</a></div>
|
||
|
|
<div><a href="poolstat.htm" title="The maxStatements property, available on the UDBConnectionPoolDataSource interface, allows for statement pooling within the connection pool. Statement pooling only has an effect on PreparedStatements and CallableStatements. Statement objects are not pooled.">DataSource-based statement pooling</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="poolprop.htm" title="You can configure the ConnectionPoolDataSource interface by using the set of properties that it provides.">ConnectionPoolDataSource properties</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|