5

Which caching strategy is faster and by how much?

1) PreparedStatement pooling (by the connection pool). No caching by the application.

for (int i=0; i<1000; i++) {
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setObject(1, someValue);
    preparedStatement.executeQuery();
    preparedStatement.close();
}

2) Application-level caching. No PreparedStatement pooling.

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i=0; i<1000; i++) {
    preparedStatement.clearParameters();
    preparedStatement.setObject(1, someValue);
    preparedStatement.executeQuery();
}
preparedStatement.close();

This question is similar to Reusing a PreparedStatement multiple times except that I'm expecting concrete benchmark results as well as taking PreparedStatement pooling into consideration.

http://drupal.org/node/550124#comment-2224630 seems to indicate that application-level caching is more efficient than PreparedStatement pooling but the difference is negligible. I'd like to see more benchmarks before making up my mind.

Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • This Sort of micro benchmark rarely elicits any useful data. The real world usage will vary enormously by usage pattern, underlying database implementation, network, memory on the database server and other stuff. Why don't you just write your code so that it works, with tests. Then if it ever proves to be too slow you can update the implementation and be sure that the software will continue to work. – time4tea Jan 19 '11 at 16:22
  • I'm trying to understand whether it is worth introducing application-level caching into a framework. This will affect the user-base at large so optimizing for a specific use-case won't really help. Is there some well-respected database benchmark we could modify instead? – Gili Jan 19 '11 at 16:42
  • Well, did any of the users request this feature? If not then perhaps nobody needs it, and you could save yourself some effort, rather than implementing a new feature that may not do much....just a thought! – time4tea Jan 19 '11 at 19:44

1 Answers1

0

Application Level caching would be way more efficient especially if you batch those executions.

Even with connection pooling, the amount of network overhead it takes to get a connection ready (back and fourth acks) and closed each time makes it not only slower, but increases the CPU level cost on both the SQL server and client server.

trilogy
  • 1,738
  • 15
  • 31
  • (As far as I understand it) that's not how connection pooling works. Connection pooling occurs on the client side. When a client closes a connection it actually remains open and the subsequent client can reuse it and any resources that are frequently recreated (such as `PreparedStatements`). – Gili Oct 10 '18 at 03:05
  • The process of creating a connection happens on both the client and server. Pooling creates multiple connections and hence multiple connections to the server. You can see it while you monitor it on the server (the number of pooled connections). The connection is closed after a query is made and another connection is added to the pool in real time. – trilogy Oct 10 '18 at 03:11
  • `PreparedStatement` aren't the same as connection pooling. I think you've confused batching to pooling. Pooling, you need a 3rd party library like HikariCP (https://github.com/brettwooldridge/HikariCP) or BoneCP. – trilogy Oct 10 '18 at 03:19