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

  <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>