Skip site navigation (1) Skip section navigation (2)

Re: Need a performance tip - Statement pooling for server

From: Xavier Poinsard <xpoinsard(at)free(dot)fr>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Need a performance tip - Statement pooling for server
Date: 2004-11-30 16:56:35
Message-ID: coi8o0$ejo$1@floppy.pyrenet.fr (view raw or flat)
Thread:
Lists: pgsql-jdbc
VIDAL Cedric wrote:
> 
>>-----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)

You take advantage of it when you reuse the same prepared statement.
For example :

   PreparedStatement ps = conn.prepareStatement("INSERT INTO t  VALUES(?)");
   for (int i=1;i<=5;i++){
      ps.setInt(1,i);
      ps.executeUpdate();
   }
   ps.close();

Or better using batch :

PreparedStatement ps = conn.prepareStatement("INSERT INTO t  VALUES(?)");
   for (int i=1;i<=5;i++){
      ps.setInt(1,i);
      ps.addBatch();
   }
   ps.executeBatch();
   ps.close();


> - 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

In response to

Responses

pgsql-jdbc by date

Next:From: Éric ParéDate: 2004-11-30 18:06:09
Subject: Re: Need a performance tip - Statement pooling for server
Previous:From: Tom LaneDate: 2004-11-30 16:38:36
Subject: Re: Bug in JDBC-Driver?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group