Cache PreparedStatement per connection or does the connection pool handle it?

Question

Which caching strategy is faster and how much?

1) Prepared pooling (through the pool of connections). No caching by 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 Union prepared.

 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 PreparedStatement several times , except that I expect specific test results, and also taking into account the preparedness of PreparedStatement.

http://drupal.org/node/550124#comment-2224630 seems to indicate that application-level caching is more efficient than the PreparedStatement pool, but the difference is not significant. I would like to see more tests before I decide.

Answer 1

This type of trace element rarely causes any useful data. Real world usage will vary greatly in usage pattern, basic database implementation, network, memory on the database server, etc.

Why don't you just write your code to work with tests. Then, if this proves to be too slow, you can update the implementation and be sure that the software will continue to work.

Answer 2

Application level caching will be more efficient, especially if you are executing these executions.

Even when pooling, the amount of network overhead required to get the connection (back and fourth) and closed each time makes it not only slower, but also increases the cost of the central processor both on the SQL server and on the client server.