Thread: 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
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 >
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)