pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepare Statement
19+ messages / 3 participants
[nested] [flat]
* Prepare Statement
@ 2004-06-14 23:32 Jie Liang <jie@stbernard.com>
2004-06-15 18:00 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-14 23:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-14 23:32 Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-15 18:00 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-15 18:00 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Prepare Statement
@ 2004-06-15 16:44 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 19+ messages in thread
From: Jie Liang @ 2004-06-15 16:44 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
Tom,
Does java.sql.PreparedStatement do the same thing as SQL command
prepare/execute ??
Which one should be used while I am using jdbc talking to postgresql? If
using both, do them help the performance?
Thanks.
Jie Liang
-----Original Message-----
From: Jie Liang
Sent: Monday, June 14, 2004 4:33 PM
To: Tom Lane
Cc: pgsql-sql@postgresql.org; pgsql-jdbc@postgresql.org
Subject: [SQL] 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-17 00:53 Jie Liang <jie@stbernard.com>
2004-06-17 04:30 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-17 00:53 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-17 00:53 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-17 04:30 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-17 04:30 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-17 18:18 Jie Liang <jie@stbernard.com>
2004-06-17 18:59 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-17 18:18 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-17 18:18 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-17 18:59 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-17 18:59 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-17 20:54 Jie Liang <jie@stbernard.com>
2004-06-17 22:25 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-17 20:54 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-17 20:54 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-17 22:25 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-17 22:25 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-17 22:50 Jie Liang <jie@stbernard.com>
2004-06-18 05:51 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-17 22:50 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-17 22:50 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-18 05:51 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-18 05:51 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-18 03:09 Jie Liang <jie@stbernard.com>
2004-06-18 05:47 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-18 03:09 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-18 03:09 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-18 05:47 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-18 05:47 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-18 16:43 Jie Liang <jie@stbernard.com>
2004-06-18 21:47 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jie Liang @ 2004-06-18 16:43 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-18 16:43 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-18 21:47 ` Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-18 21:47 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-18 16:52 Jie Liang <jie@stbernard.com>
2004-06-18 21:47 ` Re: Prepare Statement Kris Jurka <books@ejurka.com>
2004-06-18 23:07 ` Re: Prepare Statement Oliver Jowett <oliver@opencloud.com>
0 siblings, 2 replies; 19+ messages in thread
From: Jie Liang @ 2004-06-18 16:52 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-18 16:52 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-18 21:47 ` Kris Jurka <books@ejurka.com>
1 sibling, 0 replies; 19+ messages in thread
From: Kris Jurka @ 2004-06-18 21:47 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
2004-06-18 16:52 Re: Prepare Statement Jie Liang <jie@stbernard.com>
@ 2004-06-18 23:07 ` Oliver Jowett <oliver@opencloud.com>
1 sibling, 0 replies; 19+ messages in thread
From: Oliver Jowett @ 2004-06-18 23:07 UTC (permalink / raw)
To: Jie Liang <jie@stbernard.com>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Prepare Statement
@ 2004-06-21 16:28 Jie Liang <jie@stbernard.com>
0 siblings, 0 replies; 19+ messages in thread
From: Jie Liang @ 2004-06-21 16:28 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: pgsql-sql; pgsql-jdbc@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2004-06-21 16:28 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-14 23:32 Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-15 18:00 ` Kris Jurka <books@ejurka.com>
2004-06-15 16:44 Re: [SQL] Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-17 00:53 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-17 04:30 ` Kris Jurka <books@ejurka.com>
2004-06-17 18:18 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-17 18:59 ` Kris Jurka <books@ejurka.com>
2004-06-17 20:54 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-17 22:25 ` Kris Jurka <books@ejurka.com>
2004-06-17 22:50 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-18 05:51 ` Kris Jurka <books@ejurka.com>
2004-06-18 03:09 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-18 05:47 ` Kris Jurka <books@ejurka.com>
2004-06-18 16:43 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-18 21:47 ` Kris Jurka <books@ejurka.com>
2004-06-18 16:52 Re: Prepare Statement Jie Liang <jie@stbernard.com>
2004-06-18 21:47 ` Kris Jurka <books@ejurka.com>
2004-06-18 23:07 ` Oliver Jowett <oliver@opencloud.com>
2004-06-21 16:28 Re: Prepare Statement Jie Liang <jie@stbernard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox