Thread: [Fwd: PREPARE in ECPG]
<p>Paul Tilles wrote: <blockquote type="CITE">Your posting is very interesting! We are looking at porting our Informixapplications to PostgreSQL due to the increased cost of licenses. <p>I had read in the documentation that PREPARE/EXECUTEis available in Version 7.3. If this is not the case, we would not be able to port our applications to PostgreSQL.<p>BTW, what version of ecpg do you have (I have version 2.80)? <p>Can anybody out there in PostgreSQL-land pleasecomment on this? <p>Thanks. <p>Paul Tilles <p>BTS wrote: <blockquote type="CITE"><style></style><font face="Arial"><fontsize="-1">We have a large application written in C language (not C++) using embedded SQL. The number oftables in the database is about 60, with about 80 SQL indexes. Total number of SQL statements is about 800 to 1000. AllSQL statements are generated by a program generator handling the differences between Oracle/Informix/PostgreSQL. All SELECTstatements are "simple", extracting data from only one table (no joins or similar).</font></font> <font face="Arial"><fontsize="-1">The application works under Oracle (using Pro-C preprocessor) and Informix (using ESQL preprocessor).Under PostgreSQL we use the ECPG preprocessor. FYI it took us only a few weeks to port the application to PostgreSQL.</font></font><font face="Arial"><font size="-1">The application works under all these database systems and hasbeen trimmed with the necessary SQL indexes etc. For the PostgreSQL we continually run the necessary VACUUM + ANALYZEto ensure the PostgreSQL optimizer uses the proper SQL indexes (there are no table scans).</font></font> <font face="Arial"><fontsize="-1">The documentation for PostgreSQL 7.3 explains about introducing the optimized PREPARE/EXECUTEstatements, but we have discovered this is not implemented (yet) in ECPG (we are using release 7.3.2). Seecomments in "ecpg/preproc/preproc.y".</font></font> <font face="Arial"><font size="-1">We have a major installation currentlyusing Oracle, but they want to switch to PostgreSQL when the access time reaches a reasonable level. The size ofdatabase (tables + indexes) is about 120 GB, and the number of SELECTs from the tables is of the magnitude 200 to 500 millionper day, split over a number of SUN E10K machines (about 30 processors each with 1 GB RAM). (The Oracle license andannual maintenance fee on this setup are exorbitant!!)</font></font> <font face="Arial"><font size="-1">Without the optimizedPREPARE/EXECUTE we see a SELECT time of about 30 milliseconds (measured on the SUN E10K) of which most seems tobe CPU time spent inside postmaster. The similar time from Oracle is about 1 to 5 milliseconds on the same hardware. BothPostgreSQL and Oracle have been setup with a cache (shared_buffers) of 120000.</font></font> <font face="Arial"><fontsize="-1">To our customer the 30 milliseconds are not acceptable. Based on the PostgreSQL 7.3 documentationwe should expect "improved performance" when using prepared queries.</font></font> <font face="Arial"><fontsize="-1">This leads to a couple of questions regarding the optimized PREPARE/EXECUTE:</font></font> <fontface="Arial"><font size="-1">- Do you have any indication of the performance improvement (measured in percentage)?</font></font><font face="Arial"><font size="-1">- When will the optimized PREPARE/EXECUTE be implemented inECPG?</font></font><br /><font face="Arial"><font size="-1">Best Regards</font></font><br /><font face="Arial"><font size="-1">BoTveden</font></font><br /><font face="Arial"><font size="-1">Mads Madsen</font></font><br /><font face="Arial"><fontsize="-1">BTS Solutions</font></font></blockquote></blockquote>
As far as Informix, we do have PREPARE/EXECUTE in 7.3, and 7.4, due out in a few months, will have several Informix-specific improvments, like better ecpg support (and hance better 4GL support via Aubit 4GL), plus SQL improvements like WHERE CURRENT OF and WITH HOLD cursors. --------------------------------------------------------------------------- Paul Tilles wrote: > > > Paul Tilles wrote: > > > Your posting is very interesting! We are looking at porting our > > Informix applications to PostgreSQL due to the increased cost of > > licenses. > > > > I had read in the documentation that PREPARE/EXECUTE is available in > > Version 7.3. If this is not the case, we would not be able to port > > our applications to PostgreSQL. > > > > BTW, what version of ecpg do you have (I have version 2.80)? > > > > Can anybody out there in PostgreSQL-land please comment on this? > > > > Thanks. > > > > Paul Tilles > > > > BTS wrote: > > > >> We have a large application written in C language (not C++) using > >> embedded SQL. The number of tables in the database is about 60, with > >> about 80 SQL indexes. Total number of SQL statements is about 800 to > >> 1000. All SQL statements are generated by a program generator > >> handling the differences between Oracle/Informix/PostgreSQL. All > >> SELECT statements are "simple", extracting data from only one table > >> (no joins or similar). The application works under Oracle (using > >> Pro-C preprocessor) and Informix (using ESQL preprocessor). Under > >> PostgreSQL we use the ECPG preprocessor. FYI it took us only a few > >> weeks to port the application to PostgreSQL. The application works > >> under all these database systems and has been trimmed with the > >> necessary SQL indexes etc. For the PostgreSQL we continually run the > >> necessary VACUUM + ANALYZE to ensure the PostgreSQL optimizer uses > >> the proper SQL indexes (there are no table scans). The documentation > >> for PostgreSQL 7.3 explains about introducing the optimized > >> PREPARE/EXECUTE statements, but we have discovered this is not > >> implemented (yet) in ECPG (we are using release 7.3.2). See comments > >> in "ecpg/preproc/preproc.y". We have a major installation currently > >> using Oracle, but they want to switch to PostgreSQL when the access > >> time reaches a reasonable level. The size of database (tables + > >> indexes) is about 120 GB, and the number of SELECTs from the tables > >> is of the magnitude 200 to 500 million per day, split over a number > >> of SUN E10K machines (about 30 processors each with 1 GB RAM). (The > >> Oracle license and annual maintenance fee on this setup are > >> exorbitant!!) Without the optimized PREPARE/EXECUTE we see a SELECT > >> time of about 30 milliseconds (measured on the SUN E10K) of which > >> most seems to be CPU time spent inside postmaster. The similar time > >> from Oracle is about 1 to 5 milliseconds on the same hardware. Both > >> PostgreSQL and Oracle have been setup with a cache (shared_buffers) > >> of 120000. To our customer the 30 milliseconds are not acceptable. > >> Based on the PostgreSQL 7.3 documentation we should expect "improved > >> performance" when using prepared queries. This leads to a couple of > >> questions regarding the optimized PREPARE/EXECUTE: - Do you have any > >> indication of the performance improvement (measured in percentage)? > >> - When will the optimized PREPARE/EXECUTE be implemented in ECPG? > >> Best Regards > >> Bo Tveden > >> Mads Madsen > >> BTS Solutions > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Our application consists of several programs with one major running in
online mode. In online mode we start typically 40 to 60 incarnations of this
program, and it runs for months (until the machine is rebooted). Therefor
the effect on an efficient PREPARE/EXECUTE will be significant.
We know the importance of this from running our application under Informix,
because there we use the efficient PREPARE/EXECUTE.
We look forward for the ECPG implementation of
PREPARE plan_name [ (datatype [, ...] ) ] AS query
and
EXECUTE plan_name [ (parameter [, ...] ) ] INTO...
We hope to be able to write statements like:
EXEC SQL
PREPARE stm (char(5), int) as SELECT a,b,c INTO :host_a, :host_b,
:host_c
FROM mytable WHERE d=$1 AND e=$2;
...
EXEC SQL
EXECUTE stm (:host_d, :host_e);
or
EXEC SQL
PREPARE stm (char(5), int) as SELECT a,b,c FROM mytable WHERE d=$1 AND
e=$2;
...
EXEC SQL
EXECUTE stm (:host_d, :host_e) INTO :host_a, :host_b, :host_c;
Best Regards
Bo Tveden
Mads Madsen
BTS Solutions
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Paul Tilles" <Paul.Tilles@noaa.gov>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Friday, May 23, 2003 5:55 PM
Subject: Re: [Fwd: [INTERFACES] PREPARE in ECPG]
>
> As far as Informix, we do have PREPARE/EXECUTE in 7.3, and 7.4, due out
> in a few months, will have several Informix-specific improvments, like
> better ecpg support (and hance better 4GL support via Aubit 4GL), plus
> SQL improvements like WHERE CURRENT OF and WITH HOLD cursors.
>
> --------------------------------------------------------------------------
-
>
> Paul Tilles wrote:
> >
> >
> > Paul Tilles wrote:
> >
> > > Your posting is very interesting! We are looking at porting our
> > > Informix applications to PostgreSQL due to the increased cost of
> > > licenses.
> > >
> > > I had read in the documentation that PREPARE/EXECUTE is available in
> > > Version 7.3. If this is not the case, we would not be able to port
> > > our applications to PostgreSQL.
> > >
> > > BTW, what version of ecpg do you have (I have version 2.80)?
> > >
> > > Can anybody out there in PostgreSQL-land please comment on this?
> > >
> > > Thanks.
> > >
> > > Paul Tilles
> > >
> > > BTS wrote:
> > >
> > >> We have a large application written in C language (not C++) using
> > >> embedded SQL. The number of tables in the database is about 60, with
> > >> about 80 SQL indexes. Total number of SQL statements is about 800 to
> > >> 1000. All SQL statements are generated by a program generator
> > >> handling the differences between Oracle/Informix/PostgreSQL. All
> > >> SELECT statements are "simple", extracting data from only one table
> > >> (no joins or similar). The application works under Oracle (using
> > >> Pro-C preprocessor) and Informix (using ESQL preprocessor). Under
> > >> PostgreSQL we use the ECPG preprocessor. FYI it took us only a few
> > >> weeks to port the application to PostgreSQL. The application works
> > >> under all these database systems and has been trimmed with the
> > >> necessary SQL indexes etc. For the PostgreSQL we continually run the
> > >> necessary VACUUM + ANALYZE to ensure the PostgreSQL optimizer uses
> > >> the proper SQL indexes (there are no table scans). The documentation
> > >> for PostgreSQL 7.3 explains about introducing the optimized
> > >> PREPARE/EXECUTE statements, but we have discovered this is not
> > >> implemented (yet) in ECPG (we are using release 7.3.2). See comments
> > >> in "ecpg/preproc/preproc.y". We have a major installation currently
> > >> using Oracle, but they want to switch to PostgreSQL when the access
> > >> time reaches a reasonable level. The size of database (tables +
> > >> indexes) is about 120 GB, and the number of SELECTs from the tables
> > >> is of the magnitude 200 to 500 million per day, split over a number
> > >> of SUN E10K machines (about 30 processors each with 1 GB RAM). (The
> > >> Oracle license and annual maintenance fee on this setup are
> > >> exorbitant!!) Without the optimized PREPARE/EXECUTE we see a SELECT
> > >> time of about 30 milliseconds (measured on the SUN E10K) of which
> > >> most seems to be CPU time spent inside postmaster. The similar time
> > >> from Oracle is about 1 to 5 milliseconds on the same hardware. Both
> > >> PostgreSQL and Oracle have been setup with a cache (shared_buffers)
> > >> of 120000. To our customer the 30 milliseconds are not acceptable.
> > >> Based on the PostgreSQL 7.3 documentation we should expect "improved
> > >> performance" when using prepared queries. This leads to a couple of
> > >> questions regarding the optimized PREPARE/EXECUTE: - Do you have any
> > >> indication of the performance improvement (measured in percentage)?
> > >> - When will the optimized PREPARE/EXECUTE be implemented in ECPG?
> > >> Best Regards
> > >> Bo Tveden
> > >> Mads Madsen
> > >> BTS Solutions
> > >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania
19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
online mode. In online mode we start typically 40 to 60 incarnations of this
program, and it runs for months (until the machine is rebooted). Therefor
the effect on an efficient PREPARE/EXECUTE will be significant.
We know the importance of this from running our application under Informix,
because there we use the efficient PREPARE/EXECUTE.
We look forward for the ECPG implementation of
PREPARE plan_name [ (datatype [, ...] ) ] AS query
and
EXECUTE plan_name [ (parameter [, ...] ) ] INTO...
We hope to be able to write statements like:
EXEC SQL
PREPARE stm (char(5), int) as SELECT a,b,c INTO :host_a, :host_b,
:host_c
FROM mytable WHERE d=$1 AND e=$2;
...
EXEC SQL
EXECUTE stm (:host_d, :host_e);
or
EXEC SQL
PREPARE stm (char(5), int) as SELECT a,b,c FROM mytable WHERE d=$1 AND
e=$2;
...
EXEC SQL
EXECUTE stm (:host_d, :host_e) INTO :host_a, :host_b, :host_c;
Best Regards
Bo Tveden
Mads Madsen
BTS Solutions
----- Original Message -----
From: "Bruce Momjian" <pgman@candle.pha.pa.us>
To: "Paul Tilles" <Paul.Tilles@noaa.gov>
Cc: <pgsql-interfaces@postgresql.org>
Sent: Friday, May 23, 2003 5:55 PM
Subject: Re: [Fwd: [INTERFACES] PREPARE in ECPG]
>
> As far as Informix, we do have PREPARE/EXECUTE in 7.3, and 7.4, due out
> in a few months, will have several Informix-specific improvments, like
> better ecpg support (and hance better 4GL support via Aubit 4GL), plus
> SQL improvements like WHERE CURRENT OF and WITH HOLD cursors.
>
> --------------------------------------------------------------------------
-
>
> Paul Tilles wrote:
> >
> >
> > Paul Tilles wrote:
> >
> > > Your posting is very interesting! We are looking at porting our
> > > Informix applications to PostgreSQL due to the increased cost of
> > > licenses.
> > >
> > > I had read in the documentation that PREPARE/EXECUTE is available in
> > > Version 7.3. If this is not the case, we would not be able to port
> > > our applications to PostgreSQL.
> > >
> > > BTW, what version of ecpg do you have (I have version 2.80)?
> > >
> > > Can anybody out there in PostgreSQL-land please comment on this?
> > >
> > > Thanks.
> > >
> > > Paul Tilles
> > >
> > > BTS wrote:
> > >
> > >> We have a large application written in C language (not C++) using
> > >> embedded SQL. The number of tables in the database is about 60, with
> > >> about 80 SQL indexes. Total number of SQL statements is about 800 to
> > >> 1000. All SQL statements are generated by a program generator
> > >> handling the differences between Oracle/Informix/PostgreSQL. All
> > >> SELECT statements are "simple", extracting data from only one table
> > >> (no joins or similar). The application works under Oracle (using
> > >> Pro-C preprocessor) and Informix (using ESQL preprocessor). Under
> > >> PostgreSQL we use the ECPG preprocessor. FYI it took us only a few
> > >> weeks to port the application to PostgreSQL. The application works
> > >> under all these database systems and has been trimmed with the
> > >> necessary SQL indexes etc. For the PostgreSQL we continually run the
> > >> necessary VACUUM + ANALYZE to ensure the PostgreSQL optimizer uses
> > >> the proper SQL indexes (there are no table scans). The documentation
> > >> for PostgreSQL 7.3 explains about introducing the optimized
> > >> PREPARE/EXECUTE statements, but we have discovered this is not
> > >> implemented (yet) in ECPG (we are using release 7.3.2). See comments
> > >> in "ecpg/preproc/preproc.y". We have a major installation currently
> > >> using Oracle, but they want to switch to PostgreSQL when the access
> > >> time reaches a reasonable level. The size of database (tables +
> > >> indexes) is about 120 GB, and the number of SELECTs from the tables
> > >> is of the magnitude 200 to 500 million per day, split over a number
> > >> of SUN E10K machines (about 30 processors each with 1 GB RAM). (The
> > >> Oracle license and annual maintenance fee on this setup are
> > >> exorbitant!!) Without the optimized PREPARE/EXECUTE we see a SELECT
> > >> time of about 30 milliseconds (measured on the SUN E10K) of which
> > >> most seems to be CPU time spent inside postmaster. The similar time
> > >> from Oracle is about 1 to 5 milliseconds on the same hardware. Both
> > >> PostgreSQL and Oracle have been setup with a cache (shared_buffers)
> > >> of 120000. To our customer the 30 milliseconds are not acceptable.
> > >> Based on the PostgreSQL 7.3 documentation we should expect "improved
> > >> performance" when using prepared queries. This leads to a couple of
> > >> questions regarding the optimized PREPARE/EXECUTE: - Do you have any
> > >> indication of the performance improvement (measured in percentage)?
> > >> - When will the optimized PREPARE/EXECUTE be implemented in ECPG?
> > >> Best Regards
> > >> Bo Tveden
> > >> Mads Madsen
> > >> BTS Solutions
> > >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania
19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
On Fri, May 23, 2003 at 09:22:54AM -0400, Paul Tilles wrote: > > Your posting is very interesting! We are looking at porting our > > Informix applications to PostgreSQL due to the increased cost of > > licenses. Version 7.4 will contain quite a lot of Informix compatibility. > > I had read in the documentation that PREPARE/EXECUTE is available in > > Version 7.3. If this is not the case, we would not be able to port > > our applications to PostgreSQL. It is at least syntactically, i.e. the statement is not really prepared but instead the function is simulated. Michael -- Michael Meskes Email: Michael@Fam-Meskes.De ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Any word on when version 7.4 will be available? paul Michael Meskes wrote: > On Fri, May 23, 2003 at 09:22:54AM -0400, Paul Tilles wrote: > > > Your posting is very interesting! We are looking at porting our > > > Informix applications to PostgreSQL due to the increased cost of > > > licenses. > > Version 7.4 will contain quite a lot of Informix compatibility. > > > > I had read in the documentation that PREPARE/EXECUTE is available in > > > Version 7.3. If this is not the case, we would not be able to port > > > our applications to PostgreSQL. > > It is at least syntactically, i.e. the statement is not really prepared > but instead the function is simulated. > > Michael > -- > Michael Meskes > Email: Michael@Fam-Meskes.De > ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org > Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Paul Tilles wrote: > Any word on when version 7.4 will be available? pgsql-hackers is the list where such things are determined. You don't need to ask there, it's enough to serach the archives: Message-ID: <200306110357.h5B3vuB27727@candle.pha.pa.us> OK, feature freeze July 1, beta starts July 15. (Bruce Momjan) That's the latest information. The 7.4 schedule slipped already, and I don't know how long beta will take, though I'd estimate approx. 2 months. HTH & HAND, -- Gerhard