Request adding PreparedStatement caching to HikariCP

[Adam-Git-Hub]

I would like to request adding PreparedStatement caching to HikariCP.

1. PreparedStatement caching is not supported for SQL Server sqljdbc4.jar.
   jTDS does not support JDBC 4.0 nor DB mirroring
   sqljdbc4 is maintained by Microsoft
   More sql server users are likely to use HikariCP.
2. Consistency, this would simplify configuration across all jdbc drivers.
   (This would allow removing the caveat in the benchmarks about requiring PreparedStatement caching.)
3. Performance
   By leveraging the optimizations already present in HakariCP it would be faster.

Thank you,
Adam

[brettwooldridge]

I actually implemented the PreparedStatement metadata cache in jTDS like 12 years ago. How about I bring jTDS up to JDBC 4.1 instead?

[Adam-Git-Hub]

Brett,

jTDS has not been updated for almost a year.
It lacks 2014 support (anecdotal evidence suggests it works).
No DB mirroring
No Kerberos

While adding all those things would be great, it still would not address points #2 and #3, that would apply to all drivers.
Could you walk me through your thought process on what features/functionality you typically add?

Thank you,
Adam

[brettwooldridge]

Drivers have explicit knowledge of how a given database handles preparation and execution plans, and can implement caching much more efficiently. Both in terms of memory footprint and execution speed.

Consider that at the pool level, the pool has no choice but to cache JDBC PreparedStatement objects on a per connection basis. If a user says they want to cache 250 prepared statements, and the pool has 50 connections, that's 12500 objects. Or 50000 objects, with 500 statements and 100 connections. And that is not even counting things like HashMap entries.

Now, take PostgreSQL, as an counter example. PostgreSQL implements prepared statements with something called "named queries". When you prepare a statement, it is given a name. The named queries are held on the server-side, along with the associated execution plan. Named queries are global, and are shared across connections. The driver keeps a map of SQL strings to query names.

So, when you call Connection.prepareStatement(), the driver looks to see if there is a named query for the given SQL. If so, it creates a new PreparedStatement object that references the query by name on the server-side.

No PreparedStatement objects are kept in memory or cached. And it works even if the original Connection that was used to prepare the statement was closed, or the prepare was done on another Connection.

This is a level of efficiency and performance that a pool cannot match.

---

I would argue that offering a statement cache would more likely hurt the users of other databases, rather than help. I can say that with some confidence, because a large number of users switching from BoneCP or DBCP to HikariCP have asked "Where's the statement cache?!" Without even knowing that it was supported inherently by their driver. Better.

Just like nobody reads fine print, nobody reads driver property documentation. For some reason, the pool configuration is the first thing they reach for. It's not a good thing. Drivers can always do it better.

---

I do understand that there are databases with sh*tty JDBC drivers, but the vast majority of mainstream databases now provide drivers that inherently support PreparedStatement caching.

It was literally HikariCP's stand against placing responsibility of caching in the pool that prompted PostgreSQL to implement their very excellent solution.

A survey of the Top 5 databases (in order Oracle, MySQL, SQL Server, PostgreSQL, DB2) leaves only SQL Server out without prepared statement caching.

Even going to lower-tier databases, the likes of Apache Derby, Firebird, SAP HANA, etc. you find nearly all of them support prepared statement caching.

Ironically, SQL Server's JDBC driver has a property, * disableStatementPooling_, which defaults to *true_. Only true is supported currently (setting to false will throw an exception).

While I won't say the Sun will burn out before HikariCP implements statement caching, basically SQL Server is the last domino to fall. I would recommend putting pressure on Microsoft (try Twitter). The time to implement the feature in PostgreSQL was two days. If you hurry up, Microsoft JDBC 6.0 (Preview) is already out there and there is time to get this feature in. If the chance is missed, it could be another 5 years before Microsoft cuts another driver upgrade.

[brettwooldridge]

There, I started it for you. Favorite it, retweet it, respond to it (don't forget @SQLServer)!

[slyoldfox]

@brettwooldridge I would love to see jtds being bumped, last I read heard, the sqlserver driver didn't come nowhere near the performance of the outdated jtds. Then again, I have no clue if there are any recent reviews of that? Do you have an experiences with them?

[tubbynl]

For future reference; Implement PreparedStatement caching for performance is a pending issue on the mssql-jdbc project, tnx @brettwooldridge :)

microsoft/mssql-jdbc#166