Re: Need a performance tip - Statement pooling for server

> -----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