X-Original-To: pgsql-interfaces@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 6A003924EC9 for ; Fri, 23 May 2003 05:21:27 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 76757-10 for ; Fri, 23 May 2003 05:20:48 -0400 (EDT) Received: from hotmail.com (dav59.sea1.hotmail.com [207.68.162.194]) by developer.postgresql.org (Postfix) with ESMTP id D765B9256D5 for ; Fri, 23 May 2003 02:55:11 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 22 May 2003 23:55:11 -0700 Received: from 80.196.20.130 by dav59.sea1.hotmail.com with DAV; Fri, 23 May 2003 06:55:11 +0000 X-Originating-IP: [80.196.20.130] X-Originating-Email: [bts_postgresql@hotmail.com] From: "BTS" To: Subject: PREPARE in ECPG Date: Fri, 23 May 2003 08:55:07 +0200 Organization: BTS Solutions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_006C_01C32109.02A1B440" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 23 May 2003 06:55:11.0404 (UTC) FILETIME=[412E56C0:01C320F8] X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/52 X-Sequence-Number: 4170 This is a multi-part message in MIME format. ------=_NextPart_000_006C_01C32109.02A1B440 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 in= dexes. Total number of SQL statements is about 800 to 1000. All SQL stateme= nts are generated by a program generator handling the differences between O= racle/Informix/PostgreSQL. All SELECT statements are "simple", extracting d= ata 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. F= YI 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 optimiz= ed PREPARE/EXECUTE statements, but we have discovered this is not implement= ed (yet) in ECPG (we are using release 7.3.2). See comments in "ecpg/prepro= c/preproc.y". We have a major installation currently using Oracle, but they want to switc= h to PostgreSQL when the access time reaches a reasonable level. The size o= f database (tables + indexes) is about 120 GB, and the number of SELECTs fr= om 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 mill= iseconds (measured on the SUN E10K) of which most seems to be CPU time spen= t inside postmaster. The similar time from Oracle is about 1 to 5 milliseco= nds 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 Postgr= eSQL 7.3 documentation we should expect "improved performance" when using p= repared queries. This leads to a couple of questions regarding the optimized PREPARE/EXECUTE: - Do you have any indication of the performance improvement (measured in pe= rcentage)? - When will the optimized PREPARE/EXECUTE be implemented in ECPG? Best Regards Bo Tveden Mads Madsen BTS Solutions ------=_NextPart_000_006C_01C32109.02A1B440 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
We have a large application written in C l= anguage=20 (not C++) using embedded SQL. The number of tables in the database is about= 60,=20 with about 80 SQL indexes. Total number of SQL statements is about 800 to 1= 000.=20 All SQL statements are generated by a program generator handling the differ= ences=20 between Oracle/Informix/PostgreSQL. All SELECT statements are "simple",=20 extracting data from only one table (no joins or similar).
 
The application works under Oracle (using = Pro-C=20 preprocessor) and Informix (using ESQL preprocessor). Under PostgreSQL we u= se=20 the ECPG preprocessor. FYI it took us only a few weeks to port the applicat= ion=20 to PostgreSQL.
 
The application works under all these data= base=20 systems and has been trimmed with the necessary SQL indexes etc. For the=20 PostgreSQL we continually run the necessary VACUUM + ANALYZE to ensure the= =20 PostgreSQL optimizer uses the proper SQL indexes (there are no table=20 scans).
 
The documentation for PostgreSQL 7.3 expla= ins about=20 introducing the optimized PREPARE/EXECUTE statements, but we have discovere= d=20 this is not implemented (yet) in ECPG (we are using release 7.3.2). See com= ments=20 in "ecpg/preproc/preproc.y".
 
We have a major installation currently usi= ng=20 Oracle, but they want to switch to PostgreSQL when the access time reaches = a=20 reasonable level. The size of database (tables + indexes) is about 120 GB, = and=20 the number of SELECTs from the tables is of the magnitude 200 to 500 millio= n per=20 day, split over a number of SUN E10K machines (about 30 processors each wit= h 1=20 GB RAM). (The Oracle license and annual maintenance fee on this setup are= =20 exorbitant!!)
 
Without the optimized PREPARE/EXECUTE we s= ee a=20 SELECT time of about 30 milliseconds (measured on the SUN E10K) of which mo= st=20 seems to be CPU time spent inside postmaster. The similar time from Oracle = is=20 about 1 to 5 milliseconds on the same hardware. Both PostgreSQL and Oracle = have=20 been setup with a cache (shared_buffers) of 120000.
 
To our customer the 30 milliseconds are no= t=20 acceptable. Based on the PostgreSQL 7.3 documentation we should expect "imp= roved=20 performance" when using prepared queries.
 
This leads to a couple of questions regard= ing the=20 optimized PREPARE/EXECUTE:
 
- Do you have any indication of the perfor= mance=20 improvement (measured in percentage)?
 
- When will the optimized PREPARE/EXECUTE = be=20 implemented in ECPG?
 

Best Regards
Bo Tveden
Mads Madsen
BTS=20 Solutions
------=_NextPart_000_006C_01C32109.02A1B440--