Closing a PreparedStatement after a single execute – is it a design flaw?

Question

I have looked into various places, and have heard a lot of dubious claims, ranging from PreparedStatement should be preferred over Statement everywhere, even if only for the performance benefit; all the way to claims that PreparedStatements should be used exclusively for batched statements and nothing else.

However, there seems to be a blind spot in (primarily online) discussions I have followed. Let me present a concrete scenario.

---

We have an EDA-designed application with a DB connection pool. Events come, some of them require persistence, some do not. Some are artificially generated (e.g. update/reset something every X minutes, for example). Some events come and are handled sequentially, but other types of events (also requiring persistence) can (and will) be handled concurrently.

Aside from those artificially generated events, there is no structure in how events requiring persistence arrive.

This application was designed quite a while ago (roughly 2005) and supports several DBMSes. The typical event handler (where persistence is required):

• get connection from pool
• prepare sql statement
• execute prepared statement
• process the result set, if applicable, close it
• close prepared statement
• prepare a different statement, if necessary and handle the same way
• return connection to pool

If an event requires batch processing, the statement is prepared once and addBatch/executeBatch methods are used. This is an obvious performance benefit and these cases are not related to this question.

---

Recently, I have received an opinion, that the whole idea of preparing (parsing) a statement, executing it once and closing is essentially a misuse of PreparedStatement, provides zero performance benefits, regardless of whether server or client prepared statements are used and that typical DBMSes (Oracle, DB2, MSSQL, MySQL, Derby, etc.) will not even promote such a statement to prepared statement cache (or at least, their default JDBC driver/datasource will not).

Moreover, I had to test certain scenarios in dev environment on MySQL, and it seems that the Connector/J usage analyzer agrees with this idea. For all non-batched prepared statements, calling close() prints:

PreparedStatement created, but used 1 or fewer times. It is more efficient to prepare statements once, and re-use them many times

---

Due to application design choices outlined earlier, having a PreparedStatement instance cache that holds every single SQL statement used by any event for each connection in the connection pool sounds like a poor choice.

Could someone elaborate further on this? Is the logic "prepare-execute (once)-close" flawed and essentially discouraged?

P.S. Explicitly specifying useUsageAdvisor=true and cachePrepStmts=true for Connector/J and using either useServerPrepStmts=true or useServerPrepStmts=false still results in warnings about efficiency when calling close() on PreparedStatement instances for every non-batched SQL statement.

Answer 1

Is the logic prepare-execute [once]-close flawed and essentially discouraged?

I don't see that as being a problem, per se. A given SQL statement needs to be "prepared" at some point, whether explicitly (with a PreparedStatement) or "on the fly" (with a Statement). There may be a tiny bit more overhead incurred if we use a PreparedStatement instead of a Statement for something that will only be executed once, but it is unlikely that the overhead involved would be significant, especially if the statement you cite is true:

typical DBMSes (Oracle, DB2, MSSQL, MySQL, Derby, etc.) will not even promote such a statement to prepared statement cache (or at least, their default JDBC driver/datasource will not).

What is discouraged is a pattern like this:

for (int thing : thingList) {
    PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ");
    ps.setInt(1, thing);
    ps.executeUpdate();
    ps.close();
}

because the PreparedStatement is only used once and the same SQL statement is being prepared over and over again. (Although even that might not be such a big deal if the SQL statement and its executation plan are indeed cached.) The better way to do that is

PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ");
for (int thing : thingList) {
    ps.setInt(1, thing);
    ps.executeUpdate();
}
ps.close();

... or even better, with a "try with resources" ...

try (PreparedStatement ps = conn.prepareStatement(" {some constant SQL statement} ")) {
    for (int thing : thingList) {
        ps.setInt(1, thing);
        ps.executeUpdate();
    }
}

Note that this is true even without using batch processing. The SQL statement is still only prepared once and used several times.