CODEWITHSUNDEEP
javaconnection-poolingmssql-jdbchikaricp
kulatamicuda
kulatamicuda

Reputation: 1661

HikariCP statement caching on MS SQL (microsoft JDBC driver 4.1)

How can I enable statement caching in MS SQL RDBMS for HikariCP connection pool ?

For MySQL it is via :

dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.prepStmtCacheSqlLimit=2048

For PostgreSQL via:

hikari.dataSource.prepareThreshold=1

For Oracle, the following works:

dataSource.implicitCachingEnabled=true
and adjusting via  setMaxStatements() method of OracleDataSource

But I have not found anything for MS SQL 2012 and up.

Upvotes: 3

Views: 2440

Answers (1)

vanOekel
vanOekel

Reputation: 6548

Statement caching has to be provided by the database driver, HikariCP does not provide any statement caching. And as far as I can see (*), neither does the "Microsoft JDBC Driver for SQL Server". This leaves the option to use the alternative database driver jTDS. The jTDS home page does not indicate compatability with MS SQL 2012 but, the sourceforge project page does (I have not used jTDS so I am assuming the project page is more up to date (**)).

By default the jTDS driver caches 500 statements per connection (see the comments about maxStatements on the FAQ page).

Instead of using the dataSourceClassName com.microsoft.sqlserver.jdbc.SQLServerDataSource, use net.sourceforge.jtds.jdbcx.JtdsDataSource (also mentioned on this page which also indicates HikariCP was tested with the jTDS driver).

(*) The options available for the SQLServerDataSource are documented in ISQLServerDataSource but I could not find any options for statement caching.

(**) Encouraging comment in one of the last bug-reports: "We are using jTDS 1.3.1 with SQL Server 2014 with no issues."

Upvotes: 4

Related Questions