Re: PREPARE in ECPG - Mailing list pgsql-interfaces

Okay, I couldn't work out what to trim from your post so I just left it all in.

You've got 800-1000 sql statements but are they reused in a single connection?
If not you're not going to be gaining much, if anything, by using
prepare/execute so the ecpg implementation is a moot point. This is because the
prepare only prepares a statment in the backend serving that particular client.
The other side of this is, it sounds from your description that your client
might be long lived and therefore maintain database connection(s) and use the
same statements repeatedly. That's obviously the situation to take advantage
of prepare/execute.

As for the specific question you asked, well I have no idea, sorry.


-- 
Nigel J. Andrews


On Fri, 23 May 2003, BTS wrote:

> We have a large application written in C language (not C++) using embedded SQL. The number of tables in the database
isabout 60, with about 80 SQL indexes. Total number of SQL statements is about 800 to 1000. All SQL statements are
generatedby 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
PostgreSQLwe 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
thePostgreSQL 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
discoveredthis 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
reachesa reasonable level. The size of database (tables + indexes) is about 120 GB, and the number of SELECTs from the
tablesis of the magnitude 200 to 500 million per day, split over a number of SUN E10K machines (about 30 processors
eachwith 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
whichmost seems to be CPU time spent inside postmaster. The similar time from Oracle is about 1 to 5 milliseconds on
thesame 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
"improvedperformance" 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