Re: Need a performance tip - Statement pooling for server

On Tue, 30 Nov 2004, VIDAL Cedric wrote:

> Hi,
>
> On the postgresql jdbc site's todo http://jdbc.postgresql.org/todo.html, it
> is stated that
> "statement pooling to take advantage of server prepared statements" should
> be added.
>
> Is that information up to date ? Is that feature really not supported. If
> yes, does that mean, that using JDBC PreparedStatement(s) with PostgreSQL
> doesn't take avantage of server side statement pooling, and thus has no
> advantage over simple statements.
>

There is a difference between not using server side statements and not
pooling them.  Consider a function that did an insert into the database.

public void f(Connection conn, int i) throws SQLException {
  PreparedStatement ps = conn.prepareStatement("INSERT INTO t VALUES(?)");
  ps.setInt(1,i);
  ps.executeUpdate();
  ps.close();
}

In this case a prepared statement is created every time the function is
called so no real advantage comes from doing a server side preparation.
Now if the function was written as:

public void f(PreparedStatement ps, int i) throws SQLException {
  ps.setInt(1, i);
  ps.executeUpdate();
}

Then the prepared statement gets reused and potentially significant gains
can be had from doing server side preparation.  The idea behind statement
pooling is to allow a function written in the first form to get the
performance advantages of a function written in the second form.  It can
be difficult to create and manage a pool of PreparedStatements to reuse in
your application code, so it would be great if the driver could do that
for you.

Kris Jurka