CODEWITHSUNDEEP
javajdbch2
Harald
Harald

Reputation: 5113

H2 statement pooling

The reason to use prepared statements are twofold:

  1. Prevent SQL injection by adding data from the outside world only as parameters.
  2. Improve performance by letting the DB pre-parse the statement once and re-use it many times by just passing the parameters.

But prepared statements in JDBC are tied in with a connection. And we should use connection pooling. This means in a typical code sample I have to

When using JdbcConnectionPool, the simple connection pool that comes with H2, I think statements are not cached. Does it mean there is rather a negative impact on performance when using prepared statements --- which I can not really avoid due to (1) above.

Upvotes: 0

Views: 1198

Answers (1)

Hendrik
Hendrik

Reputation: 5310

H2 caches prepared statements transparently on the connection level. Meaning, as long as the SQL string is the same when you create a prepared statement and you are using the same connection it is not re-parsed, but only compared as opaque string for the cache lookup (see org.h2.jdbc.JdbcConnection#prepareCommand(...)).

You can configure the query cache size with the parameter QUERY_CACHE_SIZE—more info here.

Depending on your usage scenario, you might want to experiment with not using a connection pool at all. E.g. as embedded database, you don't really have high connection costs. You might experience a greater performance gain when using only a single connection (more cache hits; lower memory consumption, because you don't duplicate your caches). Also, many parts of H2 aren't (AFAIK) really built for concurrency.

So to answer your question:

No, I don't think here is a negative impact per se on prepared statement performance. To the contrary.

However, think about what kind of pool you want to use and how big you want it to be. Also, what kind of concurrency do you expect? Then measure both speed and memory consumption in the kind of scenario you expect to see in your live application. And... think about how relevant this really is for your application. I suspect there's probably bigger fish to fry elsewhere.

Upvotes: 2

Related Questions