4.5.2. Prepared Statements

When using the JPA annotations for queries, the result is prepared statements that will be executed against the database. Prepared statements have two phases for execution: preparation of the statement, and execution of the statement. Statement preparation involves significant CPU load so to improve throughput prepared statements can be cached. Statements can be cached either via the JDBC driver or configuration of the data source. The method recommended here is to configure the data source because it works whether or not the JDBC driver provides for caching of prepared statements. For example, the MySQL JDBC driver's prepared statement caching is done on a connection by connection basis. In the case of many connections, this implementation takes up considerably more memory, also each prepared statement must be cached for each connection in the pool.

To enable caching of prepared statement, add the following two lines to the data source configuration file, a file with the pattern *-ds.xml (where the * is usually your database, such as oracle, mysql, db2, etc.) in the directory: JBOSS_EAP_DIST/jboss-as/server/PROFILE/deploy. Note that the minimal configuration does not support data sources.

  <prepared-statement-cache-size>100</prepared-statement-cache-size>
  <shared-prepared-statements>true</shared-prepared-statements>

The first line enables the prepared statement cache and sets the size of the cache. This should be large enough to hold all the prepared statements across any and all deployed applications using this particular data source (multiple data sources can be configured). The second line states that if two requests are executed in the same transaction the prepared statement cache should return the same statement. This will avoid the use of CPU cycles in preparing the same statements over and over again, increasing throughput and decreasing response times. The actual improvement will vary according to specific circumstances but is well worth the effort.