Re: Need a performance tip - Statement pooling for server - Mailing list pgsql-jdbc

> -----Message d'origine-----
> De : pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]De la part de Kris Jurka
> Envoye : mardi 30 novembre 2004 12:37
> A : VIDAL Cedric
> Cc : 'pgsql-jdbc@postgresql.org'
> Objet : Re: [JDBC] 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

Hi Kris,

So if I get it right, i understand that
- 1 - postgresql knows how to prepare statements
- 2 - the pooling is the responsibility of the jdbc driver
- 3 - the postgresql jdbc driver doesn't implement prepared statements
pooling thus does not take advantage of
postgresql's preparing statements (your first form doesn't get the
performance advantage)
- 4 - if our application were to deal with prepared statements' pooling
itself (your second form), it would get the performance advantage of
postgresql's preparing statements

Is it so ?

thanx for the reply,

Cedric Vidal