1

Was exploring the HikariCP connection pooling library, currently in an application we use Apache DBCP2 for providing a connection pool,which allows setting the preparedstatement cache at connection pooling level by specifiying these properties:

<property name="poolPreparedStatements" value="true"/>
<property name="maxOpenPreparedStatements" value="20"/>

But HikariCP clearly mentions in the wiki, that such feature is not supported in the library and instead relies on the corresponding jdbc driver to setup the cache for preparedstatement.

Since connection pools will be shared across threads I think connection level cache for preparedstatements would be the way to go,I am not sure of the behavior of the cache at jdbcdriver level, if it does locking of some sort for the preparedstatement,causing some contention?

Any suggestions on which would be way to go if the application would need to handle high volumes of queries as part of a routine which will be executed daily?

yolob 21
  • 385
  • 4
  • 19
  • 2
    What makes you think that prepared statement caching in a connection pool would not be per connection? Because it will be, but instead of being cached **inside** the physical connection created by the JDBC driver, it will be cached in something associated with that physical connection (eg some connection pool specific object to represent a single connection that contains both the physical connection and the statement pool). It will still be per connection. – Mark Rotteveel Dec 09 '18 at 13:45
  • does that mean that setting connection pool level cache would indirectly make use of the jdbc driver level cache? If so, I didn't quite understand the benefit of providing the option to set cache for connection pools? – yolob 21 Dec 09 '18 at 14:12
  • 1
    No, first of all not all drivers support statement pooling themselves. In one case you have (pooled-connection -> (jdbc-connection -> (stmt-cache)) and in the other (pooled-connection -> (stmt-cache, jdb-connection)). In other words in the one case the pool of statements is maintained by the physical JDBC connection, in the other case it is maintained by the pooled connection (the structure used by the connection pool to hold the physical connection and associated information). However in both cases this is **per connection** (otherwise transactions, etc wouldn't work correctly). – Mark Rotteveel Dec 09 '18 at 14:19
  • ohk,thanks for clearing that,in that case, wouldn't my assumption about cache at connection pool level performing better hold up in case the scenario is 20 db connections across threads accessing cache at driver level(since there will be one physical connection at any given time) vs each connection accessing its own cache? – yolob 21 Dec 09 '18 at 14:33
  • @MarkRotteveel in case the driver itself (or its database) caches the prepared statements, isn't it free to share the cache between multiple conections, since it's not bound to the JDBC semantics where a statement is bound to a connection? I mean, what prevents it from having a shared cache of native prepared statements, and to "wrap" it into a JDBC prepared statement when any connection prepares a statement that is already in the shared cache? – JB Nizet Dec 09 '18 at 14:39
  • @JBNizet Is there a restriction already in place that discourages sharing cache across connection for preapredstatement for jdbc drivers in general, or is it again vendor specific? – yolob 21 Dec 09 '18 at 14:42
  • @yolob21 a JDBC statement is created by a connection and attached to it: when you execute it, it's executed using the connection that was used to create it, inside the current transaction started on the connection. If you close the connection, it also closes the statement. – JB Nizet Dec 09 '18 at 14:48
  • so for connection pool if cache is set at driver level, will that cache be used for executing queries receiver from one of the connections from the pool? – yolob 21 Dec 09 '18 at 15:00
  • 1
    @JBNizet In theory nothing prevents that. However don't confuse a cached compilation form and actual statement handles. In most implementation even if the same compiled form can be shared between connections, they are still associated with a statement handle and that statement handle is associated with a specific connection. From the perspective of JDBC a prepared statement cache is associated with a connection, either inside the physical connection, or within the pooled connection. How that is actually implemented is irrelevant to JDBC itself. – Mark Rotteveel Dec 09 '18 at 15:14
  • @yolob21 You are asking us to speculate and generalize. If you really want to know, you will need to test both and measure the performance, because it will differ between database, drivers, database versions and driver versions and other combinations of factors. – Mark Rotteveel Dec 09 '18 at 15:18

1 Answers1

1

Please note that PreparedStatement is cached at connection level, when using a connection pool(dbcp2 in this case), connections can be created and closed rapidly due to eviction,idle timeout operations based on the settings.

Thus in order to enable proper caching of preparedStatement I had to set:

<property name="poolPreparedStatements" value="true"/>
<property name="maxOpenPreparedStatements" value="20"/>

Before setting these, even though I tried to use a preparedStatement(via JDBCTemplate) the database would hardparse every query when tested under load of 8 threads with 2 queries over the same table with 10000 rows.

For HikariCP I didn't get a chance to check out the behavior.

yolob 21
  • 385
  • 4
  • 19