Using preferPreparedStatement

JDBC supports execution of two types of SQL statements:

• Statement

• PreparedStatement

Generally, a PreparedStatement is more efficient for the DBMS because the DBMS server can cache and reuse a PreparedStatement for multiple executions, avoiding the cost of SQL parsing and optimizer selections. To get the benefit of this caching, Waveset associates a PreparedStatement with a JDBC connection. When Waveset is going to issue the same SQL statement multiple times using the same connection, it automatically uses a PreparedStatement.

However, most of the JDBC operations performed by Waveset do not share a connection between JDBC calls. Most JDBC operations are the result of the application server processing an HTTP request through a JSP, so that the processing thread acquires a JDBC connection when needed and releases that connection when it is done. Consequently, you must do some additional work to get the benefits of using PreparedStatements.

Many application servers provide DataSource implementations that provide both connection pooling and implicit statement caching. Implicit statement caching is the ability of the DataSource or connection pool to implicitly cache JDBC statements and associate them with the connection.

If you configure Waveset to use an application server DataSource, you must have both of these features to benefit from using Waveset's preferPreparedStatement repository option. If you are not using an application server DataSource, if the DataSource is not configured to use a connection pool, or if the connection pool does not support implicit statement caching, using preferPreparedStatement causes decreased performance because the DBMS cannot reuse the statement.

Waveset code is not structured to associate a statement with a connection, except in a few circumstances, so Waveset depends on the DataSource or connection pool to perform this caching. If you configure the Waveset repository to use a DataSource or connection pool that performs implicit statement caching, and you enable preferPreparedStatement=true in the RepositoryConfiguration configuration object, Waveset can use PreparedStatement for object lock/unlock operations and most get operations, which potentially results in performance improvements. The Waveset ObjectRepository has a JMX MBean that indicates the setting of the preferPreparedStatement switch and the number of Statement/PreparedStatement requests made by the Waveset server.

You can see the current preferPreparedStatement setting in the ObjectRepository JMX MBean.