Re: [GENERAL] JDBC prepared statement is not treated as prepared statement
Hello : >Server prepared statements are kept in the private memory of the >PostgreSQL backend process. If you need a statement only once or >twice, it would be wasteful to keep it around. >The idea is that it is worth the effort only if the statement is executed >more than a couple of times. Thank you . I think it is an exciting point for PG. This make it "clever" to choice those always executed sql. Thanks! 2013/6/18 Albe Laurenz > 高健 wrote: > > I change my Java program by adding the following: > > > > org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst; > > pgt.setPrepareThreshold(1); > > > > I can see an entry is in pg_prepared_statements now. > > Good. > > > But the hyperlink's documentation made me a little confused. I also > wonder why the threshold option is > > designed . > > > > The document said: > > > > - > > > > The PostgreSQL™ server allows clients to compile sql statements that are > expected to be reused to > > avoid the overhead of parsing and planning the statement for every > execution. This functionality is > > available at the SQL level via PREPARE and EXECUTE beginning with server > version 7.3 > > > > … > > > > An internal counter keeps track of how many times the statement has > been executed and when it reaches > > the threshold it will start to use server side prepared statements. > > > > … > > > > --- > > > > What does < clients to compile sql statements > mean? > > > > I think that maybe the document just want to say: > > > > > -- > > --- > > > > Before PG import support for prepared statement, > > > > PG server must parse and plan statement every time when the client send > a request. > > > > Even when the same statement will be executed many times. > > > > > > > > After PG import support for prepared statement, > > > > When using those statement which is expected reused, by using prepared > statement mechanism, > > > > PG server can avoid overhead of parsing and planning again and again. > > > > > > > > But in order to use prepared statement, The client also must do > something: > > > > When using psql, > > > > we need to use Prepare command > > > > > > > > When using java, > > > > we use java.sql.preparedstatement, > > > > but it is not engouth: we also need to use org.postgresql.PGStatement > 's setthreshold method to let > > PG server know. > > > > > > > > The client must do something to let PG server realize that client want > PG server to use prepared > > statement. > > > > That is why the docmument say "clients to compile sql statements". > > I think that it is helpful to explain what the JDBC driver does internally. > > If you do not set the threshold or the threshold is not yet exceeded, > the driver will execute the statement as a simple statement (which > corresponds to libpq's PQexec). > > Once the threshold is exceeded, the next execution will prepare > the statement (corresponding to libpq's PQprepare) and then execute > it (like libpq's PQexecPrepared). > > Subsequent executions will only execute the named prepared statement. > > I think that "compile" in the text you quote stands for "prepare". > > > And for the threshold, > > > > If the threshold has not reached, PG server will consider the sql > statement a common one, and will > > parse and plan for it every time. > > > > Only when the threshold is reached, PG server will realize that client > need it to hold the statement > > as prepared ,then parsed it and hold the plan. > > > > > - > > > > > > > > Is my understanding right? > > Server prepared statements are kept in the private memory of the > PostgreSQL backend process. If you need a statement only once or > twice, it would be wasteful to keep it around. > The idea is that it is worth the effort only if the statement is executed > more than a couple of times. > > Yours, > Laurenz Albe >
Re: [GENERAL] JDBC prepared statement is not treated as prepared statement
高健 wrote: > I change my Java program by adding the following: > > org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst; > pgt.setPrepareThreshold(1); > > I can see an entry is in pg_prepared_statements now. Good. > But the hyperlink's documentation made me a little confused. I also wonder > why the threshold option is > designed . > > The document said: > > - > > The PostgreSQL™ server allows clients to compile sql statements that are > expected to be reused to > avoid the overhead of parsing and planning the statement for every execution. > This functionality is > available at the SQL level via PREPARE and EXECUTE beginning with server > version 7.3 > > … > > An internal counter keeps track of how many times the statement has been > executed and when it reaches > the threshold it will start to use server side prepared statements. > > … > > --- > > What does < clients to compile sql statements > mean? > > I think that maybe the document just want to say: > > -- > --- > > Before PG import support for prepared statement, > > PG server must parse and plan statement every time when the client send a > request. > > Even when the same statement will be executed many times. > > > > After PG import support for prepared statement, > > When using those statement which is expected reused, by using prepared > statement mechanism, > > PG server can avoid overhead of parsing and planning again and again. > > > > But in order to use prepared statement, The client also must do something: > > When using psql, > > we need to use Prepare command > > > > When using java, > > we use java.sql.preparedstatement, > > but it is not engouth: we also need to use org.postgresql.PGStatement 's > setthreshold method to let > PG server know. > > > > The client must do something to let PG server realize that client want PG > server to use prepared > statement. > > That is why the docmument say "clients to compile sql statements". I think that it is helpful to explain what the JDBC driver does internally. If you do not set the threshold or the threshold is not yet exceeded, the driver will execute the statement as a simple statement (which corresponds to libpq's PQexec). Once the threshold is exceeded, the next execution will prepare the statement (corresponding to libpq's PQprepare) and then execute it (like libpq's PQexecPrepared). Subsequent executions will only execute the named prepared statement. I think that "compile" in the text you quote stands for "prepare". > And for the threshold, > > If the threshold has not reached, PG server will consider the sql statement a > common one, and will > parse and plan for it every time. > > Only when the threshold is reached, PG server will realize that client need > it to hold the statement > as prepared ,then parsed it and hold the plan. > > - > > > > Is my understanding right? Server prepared statements are kept in the private memory of the PostgreSQL backend process. If you need a statement only once or twice, it would be wasteful to keep it around. The idea is that it is worth the effort only if the statement is executed more than a couple of times. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JDBC prepared statement is not treated as prepared statement
Hello:
Thanks to Laurenz. Your information is very helpful for me.
I change my Java program by adding the following:
org.postgresql.PGStatement pgt = (org.postgresql.PGStatement)pst;
pgt.setPrepareThreshold(1);
I can see an entry is in pg_prepared_statements now.
But the hyperlink's documentation made me a little confused. I also wonder
why the threshold option is designed .
The document said:
-
The PostgreSQL™ server allows clients to compile sql statements that are
expected to be reused to avoid the overhead of parsing and planning the
statement for every execution. This functionality is available at the SQL
level via PREPARE and EXECUTE beginning with server version 7.3
…
An internal counter keeps track of how many times the statement has
been executed and when it reaches the threshold it will start to use
server side prepared statements.
…
---
What does < clients to compile sql statements > mean?
I think that maybe the document just want to say:
-
Before PG import support for prepared statement,
PG server must parse and plan statement every time when the client send a
request.
Even when the same statement will be executed many times.
After PG import support for prepared statement,
When using those statement which is expected reused, by using prepared
statement mechanism,
PG server can avoid overhead of parsing and planning again and again.
But in order to use prepared statement, The client also must do something:
When using psql,
we need to use Prepare command
When using java,
we use java.sql.preparedstatement,
but it is not engouth: we also need to use org.postgresql.PGStatement 's
setthreshold method to let PG server know.
The client must do something to let PG server realize that client want PG
server to use prepared statement.
That is why the docmument say "clients to compile sql statements".
And for the threshold,
If the threshold has not reached, PG server will consider the sql statement
a common one, and will parse and plan for it every time.
Only when the threshold is reached, PG server will realize that client need
it to hold the statement as prepared ,then parsed it and hold the plan.
-
Is my understanding right?
Thanks
2013/6/17 Albe Laurenz
> 高健 wrote:
> > I have one question about prepared statement.
> > I use Java via JDBC, then send prepared statement to execute.
> > I thought that the pg_prepared_statments view will have one record
> after my execution.
> > But I can't find.
> >
> > Is the JDBC's prepared statement differ from SQL execute by prepare
> command ?
> > http://www.postgresql.org/docs/current/static/sql-prepare.html
> >
> > My simple java program is the following:
> >
> > import java.sql.*;
> >
> > public class Test01 {
> > public static void main(String argsv[]){
> > try
> > {
> >Class.forName("org.postgresql.Driver").newInstance();
> >String url = "jdbc:postgresql://localhost:5432/postgres" ;
> >Connection con =
> DriverManager.getConnection(url,"postgres","postgres" );
> >///Phase 1:-Select data from
> table---
> >System.out.println("Phase 1start");
> >String strsql = " select * from customers where cust_id = ?";
> >PreparedStatement pst=con.prepareStatement(strsql);
> >pst.setInt(1,3); //find the customer with cust_id of 3.
> >ResultSet rs = pst.executeQuery();
> >while (rs.next())
> > {
> >System.out.print("cust_id:"+rs.getInt( "cust_id"));
> >System.out.println("...cust_name:"+rs.getString(
> "cust_name" ));
> >}
> >
> >System.out.println("Phase 1end\n");
> >
> >
> >
> >///Phase 2:-Use connection again,to select data
> from data dictionary---
> >
> >System.out.println("Phase 2start");
> >strsql = "select * from pg_prepared_statements";
> >pst=con.prepareStatement(strsql);
> >rs = pst.executeQuery();
> >while (rs.next())
> >{
> > System.out.println("statement:"+rs.getString(
> "statement"));
> >}
> >System.out.println("Phase 2end\n");
> >
> >
> >
> >///Phase 3:-Use connection again,to select data
> from table-
> > --
> >System.out.println("Phase 3start");
> >strsql = "select * from customers";
> >
Re: [GENERAL] JDBC prepared statement is not treated as prepared statement
高健 wrote:
> I have one question about prepared statement.
> I use Java via JDBC, then send prepared statement to execute.
> I thought that the pg_prepared_statments view will have one record after my
> execution.
> But I can't find.
>
> Is the JDBC's prepared statement differ from SQL execute by prepare command
> ?
> http://www.postgresql.org/docs/current/static/sql-prepare.html
>
> My simple java program is the following:
>
> import java.sql.*;
>
> public class Test01 {
> public static void main(String argsv[]){
> try
> {
>Class.forName("org.postgresql.Driver").newInstance();
>String url = "jdbc:postgresql://localhost:5432/postgres" ;
>Connection con =
> DriverManager.getConnection(url,"postgres","postgres" );
>///Phase 1:-Select data from
> table---
>System.out.println("Phase 1start");
>String strsql = " select * from customers where cust_id = ?";
>PreparedStatement pst=con.prepareStatement(strsql);
>pst.setInt(1,3); //find the customer with cust_id of 3.
>ResultSet rs = pst.executeQuery();
>while (rs.next())
> {
>System.out.print("cust_id:"+rs.getInt( "cust_id"));
>System.out.println("...cust_name:"+rs.getString( "cust_name"
> ));
>}
>
>System.out.println("Phase 1end\n");
>
>
>
>///Phase 2:-Use connection again,to select data from
> data dictionary---
>
>System.out.println("Phase 2start");
>strsql = "select * from pg_prepared_statements";
>pst=con.prepareStatement(strsql);
>rs = pst.executeQuery();
>while (rs.next())
>{
> System.out.println("statement:"+rs.getString( "statement"));
>}
>System.out.println("Phase 2end\n");
>
>
>
>///Phase 3:-Use connection again,to select data from
> table-
> --
>System.out.println("Phase 3start");
>strsql = "select * from customers";
>pst=con.prepareStatement(strsql);
>rs = pst.executeQuery();
>while (rs.next())
>{
> System.out.print("cust_id:"+rs.getInt( "cust_id"));
> System.out.println("...cust_name:"+rs.getString( "cust_name" ));
> }
> System.out.println("Phase 3end\n");
> rs.close();
> pst.close();
> con.close();
>}
> catch (Exception ee)
> {
>System.out.print(ee.getMessage());
>}
> }
> }
>
>
>
> The result of it's execution is:
>
> Phase 1start
>
> cust_id:3...cust_name:Taylor
>
> Phase 1end
>
>
>
> Phase 2start
>
> Phase 2end
>
>
>
> Phase 3start
>
> cust_id:1...cust_name:Smith
>
> cust_id:2...cust_name:Brown
>
> cust_id:3...cust_name:Taylor
>
> Phase 3end
>
>
>
> That is to say: my prepared statement is not cached by PG?
>
> Then how to write a java program to made it's prepared statement realized by
> PG to treat it as a
> "prepared statement"?
>
> Thank you.
See http://jdbc.postgresql.org/documentation/head/server-prepare.html
Set the prepare threshold of a PreparedStatement and use the statement
at least as many times. Then you should see an entry in
pg_prepared_statements.
In your example, no PreparedStatement is used more than once.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
