Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

[Fwd: PREPARE in ECPG]

1 view

Skip to first unread message

Paul Tilles

unread,

May 23, 2003, 11:16:58 AM5/23/03

--------------FA871DFBF1CEF96C1FCBB0AF
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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
>

--------------FA871DFBF1CEF96C1FCBB0AF
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<body bgcolor="#FFFFFF">
 
Paul Tilles wrote:
<blockquote TYPE=CITE>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:
<blockquote TYPE=CITE><style></style>
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</blockquote>
</blockquote>

</body>
</html>

--------------FA871DFBF1CEF96C1FCBB0AF--

Bruce Momjian

unread,

May 23, 2003, 12:50:14 PM5/23/03

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.

---------------------------------------------------------------------------

--
Bruce Momjian | http://candle.pha.pa.us
pg...@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

0 new messages