mssql-jdbc MS SQL Server JDBC driver prepared statement cache performance issue with Hikari CP

Question

We have been converting our server from using Hibernate 4.2 to Hibernate 5.2.

Hibernate 5.2 requires JDBC 4.2 (Java 8), which requires us to switch to Hikari CP connection pool which is a branch from the (now deprecated) Bone CP we were previously using, since Bone CP only supports up to JDBC 4.1 (Java 7).

Unlike Bone CP, Hikari CP no longer provides prepared statement caching in the connection pool, so that now needs to be done in the JDBC driver, which for MS SQL Server means we need to move to a JDBC driver version that provides prepared statement caching in the driver, and mssql-jdbc 6.4.0 (released in January 2018) is the first one with that.

So the full change is from Hibernate 4.2 + Bone CP 0.8.0 + sqljdbc42 4.2.6420.100 MS SQL Server JDBC driver to Hibernate 5.2 + Hikari CP 2.7.8 + mssql-jdbc 6.4.0.jre8.

Unfortunately, as a result of this switchover, we're seeing about average of a 20-30% slowdown for read query performance -- which is unacceptable.

However, in the corresponding results for Oracle and MySQL with Hibernate 5.3 + Hikari + their JDBC drivers, we actually saw about a 5-15% performance improvement -- so we're reasonably sure that this isn't due to Hibernate (and it can't be Hikari directly, since that's out of the picture once the connection is handed to us).

Thus we're investigating issues around the switch from Bone CP 0.8.0 prepared statement caching to mssql-jdbc 6.4 prepared statement caching.

We've confirmed that performance gets another 10% worse if we turn the mssql-jdbc 6.4 driver prepared statement caching off, so it's doing a little good (we also confirmed in the debugger that it's actually caching things).

We've also tried tuning all the obvious available cache tuning parameters for it: statementPoolingCacheSize, serverPreparedStatementDiscardThreshold, enablePrepareOnFirstPreparedStatementCall (and also useCursors) with very little effect.

• Does anyone have any experience with the combination of Hikari CP + mssql-jdbc 6.4, or ideally with Hibernate 5 + Hikari CP + mssql-jdbc 6.4?
• Is poor prepared statement caching performance from mssql-jdbc 6.4 (in comparison what to Bone CP used to provide) a known issue? Or is mssql-jdbc slower overall for read queries than sqljdbc42 was?
• Are there any other tuning parameters for mssql-jdbc that we've missed? Can anyone suggest anything else we could try -- for example, is there another MS SQL Server JDBC driver we could try instead? (jDTS isn't an option since it's not compatible even with JDBC 4.0)

Answer 1

We had a similar situation here during the migration from Wildfly 10.0.0.Final to 17.0.1.Final.

Overall performance was improved, except on long-running processes.

In Hibernate 4.3, the cache value was 100 and after migrating to Hibernate 5.3, the value was downsized to 32 to achieve the same performance in long-running processes.

Answer 2

Cant answer all these questions directly - what helped improve performance in our case (same jdbc driver version, provided as default in Spring Boot 2.1) is to disable autocommit on Hikari. Next you need to set a Hibernate option called ‚hibernate.connection.provider_disables_autocommit‘ to true. This allows to minimize the transaction time.

Note that 7.x JDBC drivers are available - can you check if you notice the same issues?

If you‘re using Spring Boot: Also keep in mind to set ‚spring.jpa.open-in-view=false‘ to avoid unnecessary long transactions (and actually a anti-pattern).

It isnt a direct answer to your question, but it will help improve performance using Mssql and Hibernate.