Thread: Re: Need a performance tip - Statement pooling for server

Re: Need a performance tip - Statement pooling for server

From
VIDAL Cedric
Date:

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

Re: Need a performance tip - Statement pooling for server

From
Xavier Poinsard
Date:
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
>

Re: Need a performance tip - Statement pooling for server

From
"Éric Paré"
Date:
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)