How do I know if the prepared statements are cached?

Question

I am using Hikari with SQL Server 2016 and sqljdbc4-2.0.jar in the tomcat lib folder.

My configuration for db resource is as follows:

<Resource name="jdbc/SQLServerDS" auth="Container" type="javax.sql.DataSource"
              username="uname"
              password="pwd"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://server:port;DatabaseName=dbName"
              maxActive="20"
              maxIdle="10"
              validationQuery="select 1" />

My Datasource configuration is as follows:

@Bean(name = "dataSource")
    public DataSource getDataSource() throws NamingException {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setDataSourceJNDI("java:comp/env/jdbc/SQLServerDS");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("useServerPrepStmts", "true");
        config.addDataSourceProperty("cacheResultSetMetadata", "true");
        config.addDataSourceProperty("useLocalSessionState", "true");
        config.addDataSourceProperty("cacheServerConfiguration", "true");
        config.addDataSourceProperty("elideSetAutoCommits", "true");
        config.addDataSourceProperty("maintainTimeStats", "false");
        return new TransactionAwareDataSourceProxy(
                new LazyConnectionDataSourceProxy(new HikariDataSource(config)));
    }

How do I know if the preparedstatement caching is working for different connections?

I am using spring container managed transactions with hibernate v4.3.10.Final.

Also, for the caching to work, do I need to have second-level cache enabled?

Answer 1

HikariCP actually doesn't support PreparedStatement caching

others offer PreparedStatement caching. HikariCP does not. Why?

It's considered wrong implementation

Using a statement cache at the pooling layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.

Explanation:

At the connection pool layer PreparedStatements can only be cached per connection. If your application has 250 commonly executed queries and a pool of 20 connections you are asking your database to hold on to 5000 query execution plans -- and similarly the pool must cache this many PreparedStatements and their related graph of objects.

Most major database JDBC drivers already have a Statement cache that can be configured, including PostgreSQL, Oracle, Derby, MySQL, DB2, and many others. JDBC drivers are in a unique position to exploit database specific features, and nearly all of the caching implementations are capable of sharing execution plans across connections. This means that instead of 5000 statements in memory and associated execution plans, your 250 commonly executed queries result in exactly 250 execution plans in the database. Clever implementations do not even retain PreparedStatement objects in memory at the driver-level but instead merely attach new instances to existing plan IDs.

If you accept it, you shouldn't try\expect to cache PreparedStatement

If you reject it, you can use C3P0 as connection pool

About Second level cache in hibernate, it's mostly not defined in connection pool, but use relevant connection provider:

HikariCP now has a ConnectionProvider for Hibernate 4.x called HikariConnectionProvider

In order to use the HikariConnectionProvider in Hibernate 4.x add the following property to your hibernate.properties configuration file:

hibernate.connection.provider_class=com.zaxxer.hikari.hibernate.HikariConnectionProvider

As of Hibernate 4.3.6 there is an official ConnectionProvider class from Hibernate, which should be used instead of the HikariCP implementation. The class is called org.hibernate.hikaricp.internal.HikariCPConnectionProvider

Answer 2

For anyone looking out for Oracle JDBC, Quoting from the link,

Although Oracle JDBC drivers are designed with the supposition that implicit caching is enabled, this feature is not turned on by default. To enable implicit caching on the connection, you can set the implicitCachingEnabled property of the corresponding OracleConnection object to true and set the statementCacheSize property to a positive integer

To enable it on a connection pool, we need to

connectionPoolObject.setMaxStatements(10);

If you do this, statement caching will be enabled for each connection within the pool

To verify if caching is enabled,

  if (conn.getImplicitCachingEnabled())
       System.out.println("\nimplicit caching enabled");
  else
       System.out.println("\nimplicit caching disabled"); 

We can even verify at statement level,

//Checking the creation state of the prepared statement
int creationState = stmt.creationState();
switch(creationState) {
 case 0:
 System.out.println("\nCreation state: new");
 break;
case 1:
 System.out.println("\nCreation state: from the implicit cache"); 
 break;
case 2:
 System.out.println("\nCreation state: from the explicit cache"); 
 break;
}

When the statement is executed for the first time on a connection C1, Case 1 will be true and if the same statement is executed again on the same connection C1, Case 2 will be true.

Answer 3

As explained by user7294900, HikariCP does not cache prepare statements. It delegates this task to the driver.

Microsoft added prepared statement caching from v6.3.0-preview

• Prepared statement metadata caching for the JDBC driver

It can be activated like this:

connection.setStatementPoolingCacheSize(10)
connection.setDisableStatementPooling(false)

This is a nice explanation:

• PreparedStatement metadata caching (Microsoft JDBC Driver for SQL Server GitHub wiki)