Re: Need a performance tip - Statement pooling for server

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