Thread: Prepare Statement
Hi, I have a question about performance, in SQL commands: there is a prepare/execute command, document says it will improve the performance while repeatly execute a statement. In java.sql: there is a PreparedStatement object, which can store precompiled SQL statement, document says it can improve the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang
On Mon, 14 Jun 2004, Jie Liang wrote: > I have a question about performance, in SQL commands: there is a > prepare/execute command, document says it will improve the performance > while repeatly execute a statement. In java.sql: there is a > PreparedStatement object, which can store precompiled SQL statement, > document says it can improve the performance also. > If I use java jdbc to connect postgresql database, which one I should > use? Can I use both? > When using JDBC it is best to use the standard Statement/PreparedStatement interfaces. It is possible to directly use PREPARE/EXECUTE, but this can be handled by the driver. Let me give you a run down of the different driver versions and their capabilities: Current released version: can enable using PREPARE/EXECUTE behind the scenes on PreparedStatement by casting the prepared statement to PGStatement and issuing setUseServerPrepare. Current cvs version: can enable using PREPARE/EXECUTE by setting an execution threshold that will turn it on when reached. This threshold can be set at a number of levels, see the following for more information http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html Soon to be committed cvs version: can directly use server prepared statements without using the SQL level PREPARE/EXECUTE. Kris Jurka
Kris,
Thank you for your valuable response, I used the code you list
following:
import java.sql.*;
public class ServerSidePreparedStatement
{
public static void main(String args[]) throws Exception
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/test";
Connection conn =
DriverManager.getConnection(url,"test","");
PreparedStatement pstmt = conn.prepareStatement("SELECT
?");
// cast to the pg extension interface
org.postgresql.PGStatement pgstmt =
(org.postgresql.PGStatement)pstmt;
// on the third execution start using server side
statements
pgstmt.setPrepareThreshold(3);
for (int i=1; i<=5; i++)
{
pstmt.setInt(1,i);
boolean usingServerPrepare =
pgstmt.isUseServerPrepare();
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println("Execution: "+i+", Used
server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
rs.close();
}
pstmt.close();
conn.close();
}
}
Then, the compiler complaint:
ServerSidePreparedStatement.java:20: cannot resolve symbol symbol :
method setPrepareThreshold (int)
location: interface org.postgresql.PGStatement
pgstmt.setPrepareThreshold(3);
I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at
http://jdbc.postgresql.org/download.html
And had a try, I got same error msg.
I use java 1.3.1, postgresql -7.4.2, FreeBSD 4.7
What I need to do to make it work??
Thanks.
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Tuesday, June 15, 2004 11:00 AM
To: Jie Liang
Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
On Mon, 14 Jun 2004, Jie Liang wrote:
> I have a question about performance, in SQL commands: there is a
> prepare/execute command, document says it will improve the performance
> while repeatly execute a statement. In java.sql: there is a
> PreparedStatement object, which can store precompiled SQL statement,
> document says it can improve the performance also. If I use java jdbc
> to connect postgresql database, which one I should use? Can I use
> both?
>
When using JDBC it is best to use the standard
Statement/PreparedStatement
interfaces. It is possible to directly use PREPARE/EXECUTE, but this
can
be handled by the driver. Let me give you a run down of the different
driver versions and their capabilities:
Current released version: can enable using PREPARE/EXECUTE behind the
scenes on PreparedStatement by casting the prepared statement to
PGStatement and issuing setUseServerPrepare.
Current cvs version: can enable using PREPARE/EXECUTE by setting an
execution threshold that will turn it on when reached. This threshold
can
be set at a number of levels, see the following for more information
http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html
Soon to be committed cvs version: can directly use server prepared
statements without using the SQL level PREPARE/EXECUTE.
Kris Jurka
On Wed, 16 Jun 2004, Jie Liang wrote: > Kris, > Thank you for your valuable response, I used the code you list > following: > > [7.5 code example] > > Then, the compiler complaint: > ServerSidePreparedStatement.java:20: cannot resolve symbol symbol : > method setPrepareThreshold (int) > location: interface org.postgresql.PGStatement > pgstmt.setPrepareThreshold(3); I downloaded > pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at This example is from the 7.5 documentation and requires a pgdev.302.jdbcX.jar file. I mentioned this cvs example because this functionality is undocumented in the released version. In the 7.4 version the enabling of server side statements is only possible via a boolean flag at the statement level, namely PGStatement.setUseServerPrepare(true); Kris Jurka
Kirs, I re-compile with setUseServerPrepare(true), it works fine, thanks. However, reading from my log file, what I saw is that five same SELECTs with different argument, so I am wondering that the PrepareStatement really save time than individualy execute five SELECTs ??? If I use one "parepare sql command" and five "execute sql commands", the log file shown what I typed, so I think it really used server side prepared object! Any comment? Thanks. Jie Liang -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Wednesday, June 16, 2004 9:30 PM To: Jie Liang Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Prepare Statement On Wed, 16 Jun 2004, Jie Liang wrote: > Kris, > Thank you for your valuable response, I used the code you list > following: > > [7.5 code example] > > Then, the compiler complaint: > ServerSidePreparedStatement.java:20: cannot resolve symbol symbol : > method setPrepareThreshold (int) > location: interface org.postgresql.PGStatement > pgstmt.setPrepareThreshold(3); I downloaded > pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at This example is from the 7.5 documentation and requires a pgdev.302.jdbcX.jar file. I mentioned this cvs example because this functionality is undocumented in the released version. In the 7.4 version the enabling of server side statements is only possible via a boolean flag at the statement level, namely PGStatement.setUseServerPrepare(true); Kris Jurka
On Thu, 17 Jun 2004, Jie Liang wrote: > Kirs, > > I re-compile with setUseServerPrepare(true), it works fine, thanks. > However, reading from my log file, what I saw is that five same SELECTs > with different argument, so I am wondering that the PrepareStatement > really save time than individualy execute five SELECTs ??? > This is what I see in the log file: 2004-06-17 11:55:35 [23254] LOG: statement: PREPARE JDBC_STATEMENT_1(integer) AS SELECT $1 ; EXECUTE JDBC_STATEMENT_1(1) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(2) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(3) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(4) 2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(5) 2004-06-17 11:55:35 [23254] LOG: statement: DEALLOCATE JDBC_STATEMENT_1 I don't know why this would be different for you. What exact version of the server and driver are you using? Kris Jurka
Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
Then my log file were:
Select * from chr(65) as result;
Select * from chr(66) as result;
......
However, if I use:
PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.
Any comment?
Thanks.
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 11:59 AM
To: Jie Liang
Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kirs,
>
> I re-compile with setUseServerPrepare(true), it works fine, thanks.
> However, reading from my log file, what I saw is that five same
> SELECTs with different argument, so I am wondering that the
> PrepareStatement really save time than individualy execute five
> SELECTs ???
>
This is what I see in the log file:
2004-06-17 11:55:35 [23254] LOG: statement: PREPARE
JDBC_STATEMENT_1(integer) AS SELECT $1 ; EXECUTE JDBC_STATEMENT_1(1)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(2)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(3)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(4)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(5)
2004-06-17 11:55:35 [23254] LOG: statement: DEALLOCATE JDBC_STATEMENT_1
I don't know why this would be different for you. What exact version of
the server and driver are you using?
Kris Jurka
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}");
> Then my log file were:
> Select * from chr(65) as result;
> Select * from chr(66) as result;
> ......
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a bug
to me.
Kris Jurka
Hmm, intersting.
I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug
could be fixed in later version.
Thanks.
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}"); Then my
> log file were: Select * from chr(65) as result;
> Select * from chr(66) as result;
> ......
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
Kris,
I have another question, I saw some discussion regarding
PreparedStatement work with array argument, I get a error when I try to
play with it.
E.g.
I have myfunction(int[]),
So,
PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
String arr="{1,2,3}";
St.setString(1,arr};
Result rs = st.executeQuery();
Then it will complaint when it run:
Myfuntion(text) does not exist!
Did I miss something??
Thanks.
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> You are right, I modified that piece of code a little bit,
> CallableStatement stmt = conn.prepareCall("{?=call chr(?)}"); Then my
> log file were: Select * from chr(65) as result;
> Select * from chr(66) as result;
> ......
> However, if I use:
> PrepareStatement stmt = conn.prepareStatement("SELECT chr(?)");
> Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
>
> So, I am getting confusion.
> I think CallableStatement is extended from PrepareStatement, it should
> have same behaviou.
>
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with "call" before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> I have another question, I saw some discussion regarding
> PreparedStatement work with array argument, I get a error when I try to
> play with it.
> E.g.
> I have myfunction(int[]),
> So,
> PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
> String arr="{1,2,3}";
> St.setString(1,arr};
> Result rs = st.executeQuery();
>
> Then it will complaint when it run:
> Myfuntion(text) does not exist!
>
This is actually a case where prepared statements actually cause trouble.
With the directly executed SELECT myfunction('{1,2,3}'); The backend can
determine that there is only one version of myfunction so it can convert
the unkown argument type to it, but note that this won't work if
myfunction is overloaded.
With the prepared case, you must tell it what types to use when doing the
prepare. The JDBC driver doesn't have a whole lot of information to work
with, so it takes what it knows (that you called setString) and says the
argument is of type text, issuing a prepare like this: PREPARE
JDBC_STATEMENT_1(text) AS SELECT myfunction($1); At this time (before it
actually calls EXECUTE) it tries to lookup myfunction that takes a text
argument and determines there isn't one. In this case it doesn't have the
opportunity to apply any casts because we were quite clear in specifying
that it should take a text argument, not one of unknown type.
Ideally you should be using setArray, but there is no existing way to
create Array objects and I'm not sure that code would work even if there
was.
Kris Jurka
On Thu, 17 Jun 2004, Jie Liang wrote: > Hmm, intersting. > I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug > could be fixed in later version. > I suppose, but I'm going to put it pretty close to the bottom of my todo list because it still works even though it doesn't use a server prepared statement, and as I mentioned earlier the performance improvement if any will be minimal. Have you done any testing to show that you are even getting a performance gain? Kris Jurka
Nope, I think you are right. The improvement of performance will be minimal. Because that to parse "SELECT * FROM myfunction(?,?,?)" is very very quick. However, I am still thinking if I call one SELECT and one DELECT and one UPDATE and one INSERT a thousand times against same table with different arguments, should I consider performance iusse? Secondly, I assume the function should be a pre-compiled object stored on server side, doesn't it. Thanks. Jie Liang -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Thursday, June 17, 2004 10:51 PM To: Jie Liang Cc: pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Prepare Statement On Thu, 17 Jun 2004, Jie Liang wrote: > Hmm, intersting. > I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug > could be fixed in later version. > I suppose, but I'm going to put it pretty close to the bottom of my todo list because it still works even though it doesn't use a server prepared statement, and as I mentioned earlier the performance improvement if any will be minimal. Have you done any testing to show that you are even getting a performance gain? Kris Jurka
So, I think that PreparedStatement should have a way at least case a
String to an Array or a way to create a Array, because of
conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
useful.
Comment?
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Thursday, June 17, 2004 10:47 PM
To: Jie Liang
Cc: pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
> Kris,
> I have another question, I saw some discussion regarding
> PreparedStatement work with array argument, I get a error when I try
> to play with it. E.g.
> I have myfunction(int[]),
> So,
> PrepareStatement st = conn.prepareStatment("SELECT myfunction(?)");
> String arr="{1,2,3}";
> St.setString(1,arr};
> Result rs = st.executeQuery();
>
> Then it will complaint when it run:
> Myfuntion(text) does not exist!
>
This is actually a case where prepared statements actually cause
trouble.
With the directly executed SELECT myfunction('{1,2,3}'); The backend can
determine that there is only one version of myfunction so it can convert
the unkown argument type to it, but note that this won't work if
myfunction is overloaded.
With the prepared case, you must tell it what types to use when doing
the prepare. The JDBC driver doesn't have a whole lot of information to
work with, so it takes what it knows (that you called setString) and
says the argument is of type text, issuing a prepare like this: PREPARE
JDBC_STATEMENT_1(text) AS SELECT myfunction($1); At this time (before
it actually calls EXECUTE) it tries to lookup myfunction that takes a
text argument and determines there isn't one. In this case it doesn't
have the opportunity to apply any casts because we were quite clear in
specifying that it should take a text argument, not one of unknown type.
Ideally you should be using setArray, but there is no existing way to
create Array objects and I'm not sure that code would work even if there
was.
Kris Jurka
On Fri, 18 Jun 2004, Jie Liang wrote: > However, I am still thinking if I call one SELECT and one DELECT and one > UPDATE and one INSERT a thousand times against same table with different > arguments, should I consider performance iusse? Right, this is a case where some benefits can be found, but remember the premature optimization adage. > > Secondly, I assume the function should be a pre-compiled object stored > on server side, doesn't it. > I depends on the language the function is written. plpgsql caches plans, but not all procedural languages do. Kris Jurka
On Fri, 18 Jun 2004, Jie Liang wrote:
> So, I think that PreparedStatement should have a way at least case a
> String to an Array or a way to create a Array, because of
> conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
> useful.
Right, this is a known issue. It's on the list.
Kris Jurka
Jie Liang wrote:
> So, I think that PreparedStatement should have a way at least case a
> String to an Array or a way to create a Array, because of
> conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
> useful.
One way that might work in the current driver is to use PGobject, which
lets you specify the underlying typename to use:
stmt = conn.preparedStatement("SELECT myfunction(?)");
org.postgresql.util.PGobject obj = new PGobject();
obj.setValue("'{1,2,3}'");
obj.setType("int[]");
stmt.setObject(1, obj);
Untested, but in theory this should work even with
setUseServerPrepare(true) in effect.
Also, if you search the archives, quite some time ago (a year?) I posted
a patch that implemented setArray() better, so that you could pass your
own Array implementation to it and have things work correctly. It's out
of date, but might give you a starting point.
-O
Nope, it will get same error msg:
Myfunction(text) does net exist
Jie Liang
-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com]
Sent: Friday, June 18, 2004 4:07 PM
To: Jie Liang
Cc: Kris Jurka; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
Jie Liang wrote:
> So, I think that PreparedStatement should have a way at least case a
> String to an Array or a way to create a Array, because of
> conn.prepareStatement("SELECT myfunction('{1,2,3}')") is NOT very
> useful.
One way that might work in the current driver is to use PGobject, which
lets you specify the underlying typename to use:
stmt = conn.preparedStatement("SELECT myfunction(?)");
org.postgresql.util.PGobject obj = new PGobject();
obj.setValue("'{1,2,3}'");
obj.setType("int[]");
stmt.setObject(1, obj);
Untested, but in theory this should work even with
setUseServerPrepare(true) in effect.
Also, if you search the archives, quite some time ago (a year?) I posted
a patch that implemented setArray() better, so that you could pass your
own Array implementation to it and have things work correctly. It's out
of date, but might give you a starting point.
-O
Jie Liang wrote: > Nope, it will get same error msg: > Myfunction(text) does net exist Hm, indeed. I just looked at the code and the plain setObject() path seems slightly broken: it always passes PG_TEXT, not the PGobject's type. However, the setObject variant that takes a target SQL type seems ok. Try this: > stmt.setObject(1, obj, Types.OTHER); -O
It works. Thanks. Jie Liang -----Original Message----- From: Oliver Jowett [mailto:oliver@opencloud.com] Sent: Friday, June 18, 2004 6:39 PM To: Jie Liang Cc: Kris Jurka; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Prepare Statement Jie Liang wrote: > Nope, it will get same error msg: > Myfunction(text) does net exist Hm, indeed. I just looked at the code and the plain setObject() path seems slightly broken: it always passes PG_TEXT, not the PGobject's type. However, the setObject variant that takes a target SQL type seems ok. Try this: > stmt.setObject(1, obj, Types.OTHER); -O
Does plperl catch the plan also?
Thanks.
Jie Liang
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Friday, June 18, 2004 2:47 PM
To: Jie Liang
Cc: pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Prepare Statement
On Fri, 18 Jun 2004, Jie Liang wrote:
> However, I am still thinking if I call one SELECT and one DELECT and
> one UPDATE and one INSERT a thousand times against same table with
> different arguments, should I consider performance iusse?
Right, this is a case where some benefits can be found, but remember the
premature optimization adage.
>
> Secondly, I assume the function should be a pre-compiled object stored
> on server side, doesn't it.
>
I depends on the language the function is written. plpgsql caches
plans,
but not all procedural languages do.
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html