Re: Need a performance tip - Statement pooling for server

From: Kris Jurka <books(at)ejurka(dot)com>
To: VIDAL Cedric <c-vidal(at)jm-bruneau(dot)fr>
Cc: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Need a performance tip - Statement pooling for server
Date: 2004-11-30 11:37:14
Message-ID: Pine.BSO.4.56.0411300630250.8244@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2004-11-30 11:41:32 Re: Bug in JDBC-Driver?
Previous Message Dave Cramer 2004-11-30 11:32:03 Re: Bug in JDBC-Driver?