Re: [Fwd: PREPARE in ECPG] - Mailing list pgsql-interfaces

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