Statement Caching

Statement caching is a way to improve client performance by caching and reusing PreparedStatement and CallableStatement objects. When you re-use a PreparedStatement or CallableStatement, you avoid the overhead of statement preparation which involves work in both the driver as well as the server to prepare the query for execution. As a result, you can get a performance benefit when re-using statements.

Note: Statement caching only works for PreparedStatement and CallableStatement objects. A basic Statement object cannot be cached as it does not have a set SQL string which is saved on the server.

Each physical connection to the server will have its own Statement cache. Statements are cached with a key. For implicit caching the key used is the SQL string used for the Statement. For an explicit cache, you can specific the key you want to save the Statement with.

Enabling Statement Caching

You can enable Statement caching in two ways. You can enable it by setting the statement cache size by calling the method IfxConnection.setStatementCacheSize or you can use the connection parameter IFX_PREPAREDSTATEMENT_CACHE_SIZE to the size you want set. A size of 0 disables the cache for this connection. By default, the statement cache is disabled (size is 0).

Connection conn = Driver.getConnection("jdbc:informix-sqli://localhost:9889/testdb:user=informix;password=informix; IFX_PREPAREDSTATEMENT_CACHE_SIZE=20");
((IfxConnection)conn).setStatementCacheSize(20); // Can set the cache size at any time on the connection

Explicitly Statement Caching

You can explicitly save a statement into the case with a specified key. You must have already enabled the cache on the parent Connection object. You can then close any PreparedStatement or CallableStatement with closeWithKey(String uniqueKey). This caches the statement with your key instead of the SQL statement.

You can retrieve a statement using the IfxConnection.getStatementWithKey(String key) or IfxConnection.getCallWithKey(String key) depending on if you are retrieving a PreparedStatement or a CallableStatement.

PreparedStatment p = conn.prepareStatement("SELECT * FROM systables");
((IfxPreparedStatement)p).closeWithKey("sample-key");

p = ((IfxConnection)conn).getStatementWithKey("sample-key");

Disabling Caching for a single Statement

Sometimes you do not want a Statement to be cached. In this case, you can use the JDBC Statement API to turn off the pool setting before you close the connection.

stmt.setPoolable(false);
stmt.close();

Example

Properties prop = new Properties();
prop.setProperty("IFX_PREPAREDSTATEMENT_CACHE_SIZE", "20"); //save 20 prepared statements
try(Connection con = DriverManager.getConnection(“JDBC-url-here", prop)) {
	try(PreparedStatement p = con.prepareStatement("INSERT INTO mytable VALUES(?)")) {
		 //do work
	} //prepared statement closed/cached
	//SQL matches, so we get back the prepared statement handle to reuse
	try(PreparedStatement p = con.prepareStatement("INSERT INTO mytable VALUES(?)")) {
		 //do work
	} //prepared statement closed/cached
}