Thread: Need a performance tip - Statement pooling for server prepared st atements

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.

Either way, is there any other performance tip that could help us achieve
better performances ?

Any information on that matter would be greatly appreciated,

Thanx a lot,

Cédric Vidal

PS: We're using PostgreSQL/JDBC through Hibernate.

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