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

I apologize for this psycho remark, but I always feel afraid of seeing
re-use of PreparedStatement objects without calling the
clearParameters() function that resets all parameters...
Just thought It might be worth mentionning.

--
Éric Paré for/pour LexUM
Université de Montréal
Centre de recherche en droit public
C.P. 6128, succ. Centre-ville
Montréal (Qc) Canada H3C 3J7
+1 514-343-6111 #0873
paree@lexum.umontreal.ca
VIDAL Cedric wrote:
>
>>-----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)

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
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)