8.5.2 Using statement pooling

(1) Advantages of using a statement pooling

The process of generating statements such as SQL statements and stored procedure that is necessary for accessing the database increase the load. The use of a statement pooling helps to reduce the load. Statement pooling is a functionality that enhances the processing efficiency by pooling in advance the PreparedStatement and the CallableStatement that are generated once, and reusing them. The processing efficiency is enhanced as compared to the processing efficiency for generating the statements each time the database is accessed.

Now, the PreparedStatement and the CallableStatement are the instances of the java.sql.PreparedStatement and java.sql.CallableStatement, respectively that are the APIs of JDBC.

To Page Top

(2) Guidelines for setup

To use statement pooling, it is necessary to consider the following points before starting the application:

• Use a java.sql.Connection#prepareStatement method having the same signature.

• Specify the same argument value in the java.sql.Connection#prepareStatement method.

Statement pooling does not work properly in the applications that do not meet the above conditions.

Use statement pooling after understanding its relationship with the connection pooling. The points you must note while configuring the application and setting up the environment are explained as follows:

• The PreparedStatement and the CallableStatement are pooled for each instance of the physical connection. If there are multiple connections pooled when acquiring a connection, it is likely that a connection, for which statements are not yet pooled, may be allocated.

• You need to set the pool size keeping in mind the maximum value of each connection of the JDBC driver that is used, in order to pool the instances of the respective statements.

The following figure shows the relationship between the connection pool and the statement pool:

Figure 8‒7: Relationship between a connection pool and a statement pool

Determine the size of a pool in the case of statement pooling after considering its relationship with connection pooling according to the contents of the business process. For example, in the case of accessing different tables and using a different SQL even when the same database is used in different business processes, it may be more effective to prepare separate DB Connectors for each business process and prepare the connection pool and the statement pool of only the size required for that business instead of increasing the size of the statement pool and connection pool with one DB Connector.

Figure 8‒8: Use of a connection pool and statement pool according to the business process

If you decrease the size of the pool by using multiple DB Connectors, then there is no surplus of connections pooled for each business that is likely to result in a shortage of connections during the peak access. Estimate in detail the number of concurrent executions of the business and tune the settings to avoid any shortage of connections as far as possible. In addition, tune the size of the pool for each business depending on how many statements are used.

Determine the size of the statement pool when a single DB Connector is used by adding up the following values. Estimate the pool size considering that DB Connector uses one statement pool internally.

How to calculate the size of PreparedStatement pool and the guidelines for its setup

Assume the resource limit of the JDBC driver of each connection as the maximum value, and calculate the size based on the number of usages of PreparedStatement that uses the same DB Connector. Set the resource limit of the JDBC driver according to the limit value of the JDBC driver that is being used.

The number of usages is obtained by adding up following numbers:

• Number of invocations for java.sql.Connection#prepareStatement method by specifying different arguments from the servlet and JSP

• Number of invocations for java.sql.Connection#prepareStatement method by specifying different arguments from the Session Bean and the Entity Bean (BMP)

• Number of SQLs used in PreparedStatement internally by the J2EE server for the Entity Bean (CMP)

• Number of invocations for java.sql.Connection#prepareStatement method by specifying different arguments from the Message-driven Bean

How to calculate the size of CallableStatement pool and the guidelines for its setup

Assume the resource limit of the JDBC driver for each connection as the maximum value, and calculate the size based on the number of usages of CallableStatement that use the same DB Connector. Set the resource limit of the JDBC driver according to the limit value of the JDBC driver that is being used.

The number of usages is obtained by adding up the following values:

• Number of invocations for java.sql.Connection#prepareCall method by specifying different arguments from the servlet and JSP

• Number of invocations for java.sql.Connection#prepareCall method by specifying different arguments from the Session Bean and the Entity Bean (BMP)

• Number of invocations for java.sql.Connection#prepareCall method by specifying different arguments from the Message-driven Bean

To Page Top