[Fwd: PREPARE in ECPG]

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
>