Statement Caching

Statement Caching#

Statement Caching is a feature designed to enhance performance by caching and reusing SQL statements when they are frequently executed. This functionality can be enabled without requiring modifications to the existing program. PreparedStatement and CallableStatement objects are the targets of caching, while Statement objects are not cached. Statement Caching is managed and configured at the Connection level.

Basic Operation#

When executing the close() method of the Statement interface, a statement is cached. Subsequently, upon executing the prepareStatement() or prepareCall() methods, the system searches for an identical statement within the cached statements. If a matching statement is found in the cache, it returns the corresponding Statement object; otherwise, it creates a new Statement object and returns it.

Criteria for Identical Statements#

The matching criteria for identical statements are as follows:

SQL Consistency: The SQL string must be identical.
Statement Type Matching: The statement type (e.g., PreparedStatement or CallableStatement) must match.
ResultSet Attributes: Attributes of ResultSet objects generated by the statement (such as Scrollable, Concurrency, and Holdability) must be consistent.

Handling of Cached Statements#

When statement caching is enabled, the close() method of the statement interface caches the statement instead of closing it. That is, the statement is not physically closed.

Exceptions to Caching#

Despite the caching mechanism, there are exceptions where a statement is physically closed:

Connection Closure: When the close() method of the Connection interface is executed, all statement objects created from that connection are physically closed. However, pooled connections are not closed.
isPoolable() is false: When closing the statement object whose isPoolable() is false, the statement is closed.
Cache Size Limit: If the number of cached statements exceeds the configured limit defined by stmt_cache_size, the least recently accessed statement object is physically closed by the LRU(Least Recently Used) algorithm.

Usage#

To make use of Statement Caching, the following connection properties must be configured.

stmt_cache_enable

By default, stmt_cache_enable is set to false, so you should configure it as follows to enable the Statement Caching feature:
```
Properties sProps = new Properties();
...
sProps.put("stmt_cache_enable", "true");
...
```
stmt_cache_size
stmt_cache_sql_limit

If you want to prevent caching of specific statements while Statement Caching is active, the setPoolable(false) method of the Statement interface should be invoked.
```
...
sStmt.setPoolable(false);
...
```

Code Example#

If specific SQL statements need to be executed repeatedly, you can enable the statement caching feature to reuse cached statements for better performance, as shown in the following example.

...
Properties        sProps   = new Properties();
...        
sProps.put("stmt_cache_enable", "true");
...
Connection        sCon     = DriverManager.getConnection( sURL, sProps );
Statement         sStmt    = sCon.createStatement();
...
 for (int i = 0; i < 100; i++)
 {
     PreparedStatement sPreStmt = sCon.prepareStatement( "INSERT INTO T1 VALUES(1,1)" );
     sPreStmt.execute();
     sPreStmt.close();
  }
 /* Finalize process */
 sStmt.close();
 sCon.close();
...

Cautions#

When the Statement Caching feature is enabled, executing DDL operations on database objects can lead to errors, so caution is advised.
Statement Caching and the defer_prepares feature cannot be used concurrently. Ensure that they are not enabled together.
Be cautious not to duplicate the usage of the Statement Caching feature with statement pooling functionality offered by other libraries, such as DBCP's poolPreparedStatement.
Enabling the Statement Caching feature may increase the memory usage on both the server and client sides. It is recommended to tune this by adjusting the stmt_cache_size and stmt_cache_sql_limit properties appropriately. Additionally, consider configuring the Java heap memory size as needed.

Administrator

Reference

Developer

Tools

External Tools

Statement Caching