2

As I understand it, each time a PreparedStatement is initialized, the statement gets cached in the memory allocated for the database connection. Therefore, if one initializes excessive PreparedStatement variables there is a risk of overflowing the memory available to the connection.

  1. Is the cached memory freed by calling close() on the PreparedStatement instances?

  2. Do two PreparedStatement containing identical SQL create duplicate caching events, or is the database smart enough not to cached a new instance of a duplicate PreparedStatement?

Example 1, would this overflow connection memory?:

while (true) {
    PreparedStatement ps = connection.prepareStatement("SELECT id + ? FROM tbl");
    ps.setDouble(1, Math.random());
    ps.executeQuery();
    ps.close();
}

If it would, then what about this?:

while (true) {
    PreparedStatement ps = connection.prepareStatement("SELECT id FROM tbl");
    ps.executeQuery();
    ps.close();
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Ray Zhang
  • 1,411
  • 4
  • 18
  • 36
  • "Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources." – K.Nicholas Jun 06 '19 at 22:42
  • Possible duplicate of [Do I need to close PreparedStatement](https://stackoverflow.com/questions/14546592/do-i-need-to-close-preparedstatement) – K.Nicholas Jun 06 '19 at 22:43
  • Your question is too broad. You are asking two different things (freeing memory on close, and sharing prepared statement information between `PreparedStatement` instances) for two different databases (MySQL and PostgreSQL). Please focus on one thing per question. – Mark Rotteveel Jun 07 '19 at 07:40

2 Answers2

0

Answer to both: That entirely depends on the JDBC driver.

E.g. for PostgreSQL JDBC driver, see Server Prepared Statements:

The driver uses server side prepared statements by default when PreparedStatement API is used. In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold connection property). An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements.

It is generally a good idea to reuse the same PreparedStatement object for performance reasons, however the driver is able to server-prepare statements automatically across connection.prepareStatement(...) calls.

[...]

Server-prepared statements consume memory both on the client and the server, so pgjdbc limits the number of server-prepared statements per connection. It can be configured via preparedStatementCacheQueries (default 256, the number of queries known to pgjdbc), and preparedStatementCacheSizeMiB (default 5, that is the client side cache size in megabytes per connection). Only a subset of statement cache is server-prepared as some of the statements might fail to reach prepareThreshold.

For MS SQL Server JDBC driver, see Prepared Statement Metadata Caching for the JDBC Driver.

For MySQL JDBC driver, see answer to Prepared Statement Cache with MySQL & JDBC.

I don't see any such feature for Oracle JDBC driver.

Community
  • 1
  • 1
Andreas
  • 154,647
  • 11
  • 152
  • 247
0
  1. Not all drivers support precompilation.
  2. Both JDBC and Database will perform cache eviction when necessary.

Example for Oracle JDBC driver:

Source: https://docs.oracle.com/cd/B28359_01/java.111/b31224/stmtcach.htm

Statements are removed from the cache to conform to the maximum size using a Least Recently Used (LRU) algorithm.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
joy
  • 327
  • 4
  • 11