Show TOC

Background documentationStatement Pooling Locate this document in the navigation structure

 

Statement pooling improves performance by caching SQL statements that are used repeatedly. The benefit of this cache is to prepare frequently used statements only once, but execute them multiple times, thus reducing the overall number of parse calls that must be issued against the database.

Features

The general JDBC code to execute a query looks as follows (con is a Connection object that we have obtained from a DataSource):

Example Example

  1. PreparedStatement ps = con.prepareStatement("select name from employee where empno = ?");
ps.setInt(1, 4711);
…
ResultSet rs = ps.executeQuery();
…
ps.close();
End of the code.

This code snippet could be part of a servlet or a JSP, which is triggered repeatedly in the AS Java (possibly with different empno values). This means that the same SQL statement is sent to the database and prepared any time the servlet or JSP is called. Preparing an SQL statement for the database, however, means to parse the SQL statement and choose the optimal execution plan, which is a rather expensive operation on most database platforms, and hence may lead to a considerable performance overhead in the long run.

Statement Pooling enables the applications to reuse a PreparedStatement object in a similar way as they can reuse a database connection when connection pooling is enabled. The reuse is completely transparent to the application. From the application's viewpoint, using a PreparedStatement object that participates in statement pooling is exactly the same as using one that does not. Changes to application code are not required. If an application closes a PreparedStatement object, it can still call Connection.prepareStatement()to use it again. The only visible effect of statement pooling is a possible performance improvement.

A statement pool instance is associated with a physical database connection and it caches PreparedStatement and CallableStatement objects that are created using this connection. Plain Statement objects are not cached, however, because typically they are not reused. Whenever a prepareStatement() or prepareCall() method is called on a connection, the Native JDBC driver automatically searches the associated statement pool for a matching statement. The matching criteria are as follows:

  • The statement text must be identical (case-sensitive) to the one in the cache.

  • The statement type must be the same (prepared or callable).

  • The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable).

If a matching statement is found in the pool, a new PreparedStatement object is created as a logical handle for the pooled statement and returned to the caller. If no matching statement is found, then the prepare call is passed through to the database to prepare the statement and create a new PreparedStatement object. The new statement is pooled when the close() method is called on it.

The following graphics illustrate the principles of statement pooling.

This graphic is explained in the accompanying text.

Figure 1. Preparing A SQL Statement

This graphic is explained in the accompanying text.

Figure 2. Closing A Prepared Statement