From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Paul Tilles <Paul.Tilles@noaa.gov>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [Fwd: PREPARE in ECPG]
Date: Fri, 23 May 2003 11:55:17 -0400 (EDT)
Message-ID: <200305231555.h4NFtIh25359@candle.pha.pa.us> (raw)
In-Reply-To: <3ECE20AE.249E0594@noaa.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: pgman@candle.pha.pa.us, Paul.Tilles@noaa.gov
Subject: Re: [Fwd: PREPARE in ECPG]
In-Reply-To: <200305231555.h4NFtIh25359@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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