X-Original-To: pgsql-interfaces@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 12706924D05 for ; Wed, 28 May 2003 02:59:10 -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 30928-02 for ; Wed, 28 May 2003 02:59:03 -0400 (EDT) Received: from hotmail.com (dav73.sea1.hotmail.com [207.68.162.208]) by developer.postgresql.org (Postfix) with ESMTP id 2333A924D07 for ; Wed, 28 May 2003 02:59:03 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 27 May 2003 23:59:02 -0700 Received: from 80.196.20.130 by dav73.sea1.hotmail.com with DAV; Wed, 28 May 2003 06:59:02 +0000 X-Originating-IP: [80.196.20.130] X-Originating-Email: [bts_postgresql@hotmail.com] From: "BTS" To: Subject: Re: PREPARE in ECPG Date: Wed, 28 May 2003 08:58:56 +0200 Organization: BTS Solutions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A7_01C324F7.5EAFFDE0" 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: 28 May 2003 06:59:02.0362 (UTC) FILETIME=[9EE87BA0:01C324E6] X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/81 X-Sequence-Number: 4199 This is a multi-part message in MIME format. ------=_NextPart_000_00A7_01C324F7.5EAFFDE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Our application consists of several programs with one major running in online mode. In online mode we start typically 40 to 60 incarnations of this program, and it runs for months (until the machine is rebooted). Therefor the effect on an efficient PREPARE/EXECUTE will be significant. We know the importance of this from running our application under Informix, because there we use the efficient PREPARE/EXECUTE. We look forward for the ECPG implementation of PREPARE plan_name [ (datatype [, ...] ) ] AS query and EXECUTE plan_name [ (parameter [, ...] ) ] INTO... We hope to be able to write statements like: EXEC SQL PREPARE stm (char(5), int) as SELECT a,b,c INTO :host_a, :host_b, :host_c FROM mytable WHERE d=3D$1 AND e=3D$2; ... EXEC SQL EXECUTE stm (:host_d, :host_e); or EXEC SQL PREPARE stm (char(5), int) as SELECT a,b,c FROM mytable WHERE d=3D$1 AND e=3D$2; ... EXEC SQL EXECUTE stm (:host_d, :host_e) INTO :host_a, :host_b, :host_c; Best Regards Bo Tveden Mads Madsen BTS Solutions ----- Original Message -----=20 From: "Nigel J. Andrews" To: "BTS" Cc: Sent: Friday, May 23, 2003 12:14 PM Subject: Re: [INTERFACES] PREPARE in ECPG > > 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. > > > --=20 > 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 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ------=_NextPart_000_00A7_01C324F7.5EAFFDE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Ou= r application=20 consists of several programs with one major running in
online mode. In o= nline=20 mode we start typically 40 to 60 incarnations of this
program, and it ru= ns=20 for months (until the machine is rebooted). Therefor
the effect on an=20 efficient PREPARE/EXECUTE will be significant.

We know the importanc= e of=20 this from running our application under Informix,
because there we use t= he=20 efficient PREPARE/EXECUTE.

We look forward for the ECPG implementati= on=20 of
  PREPARE plan_name [ (datatype [, ...] ) ] AS query
and
&= nbsp;=20 EXECUTE plan_name [ (parameter [, ...] ) ] INTO...

We hope to be abl= e to=20 write statements like:
  EXEC SQL
    PREPARE stm= =20 (char(5), int) as SELECT a,b,c INTO :host_a,=20 :host_b,
:host_c
        &nbs= p;            &= nbsp;           =20 FROM mytable WHERE d=3D$1 AND e=3D$2;
...
  EXEC=20 SQL
    EXECUTE stm (:host_d,=20 :host_e);

or

  EXEC SQL
    PREPARE st= m=20 (char(5), int) as SELECT a,b,c FROM mytable WHERE d=3D$1=20 AND
e=3D$2;
...
  EXEC SQL
    EXECUTE stm= =20 (:host_d, :host_e) INTO :host_a, :host_b, :host_c;


Best Regards<= BR>Bo=20 Tveden
Mads Madsen
BTS Solutions

----- Original Message -----= =20
From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
To: "BTS" <bts_postgresql@hotmail.compgsql-interfaces@postgresql.org>
Sent: Friday, May 23, 2003 12:14 PM
Subject: Re: [INTER= FACES]=20 PREPARE in ECPG


>
> Okay, I couldn't work out what to t= rim=20 from your post so I just left it
all in.
>
> You've got 800-= 1000=20 sql statements but are they reused in a single
connection?
> If no= t=20 you're not going to be gaining much, if anything, by using
>=20 prepare/execute so the ecpg implementation is a moot point. This is
beca= use=20 the
> prepare only prepares a statment in the backend serving that=20 particular
client.
> The other side of this is, it sounds from you= r=20 description that your
client
> might be long lived and therefore= =20 maintain database connection(s) and use
the
> same statements=20 repeatedly. That's obviously the situation to take
advantage
> of= =20 prepare/execute.
>
> As for the specific question you asked, we= ll I=20 have no idea, sorry.
>
>
> --
> Nigel J.=20 Andrews
>
>
> On Fri, 23 May 2003, BTS wrote:
>
= >=20 > We have a large application written in C language (not C++)=20 using
embedded SQL. The number of tables in the database is about 60, wi= th=20 about
80 SQL indexes. Total number of SQL statements is about 800 to 100= 0.=20 All SQL
statements are generated by a program generator handling the=20 differences
between Oracle/Informix/PostgreSQL. All SELECT statements ar= e=20 "simple",
extracting data from only one table (no joins or similar).
= >=20 >
> > The application works under Oracle (using Pro-C preproces= sor)=20 and
Informix (using ESQL preprocessor). Under PostgreSQL we use the=20 ECPG
preprocessor. FYI it took us only a few weeks to port the applicati= on=20 to
PostgreSQL.
> >
> > The application works under all= =20 these database systems and has been
trimmed with the necessary SQL index= es=20 etc. For the PostgreSQL we
continually run the necessary VACUUM + ANALYZ= E to=20 ensure the PostgreSQL
optimizer uses the proper SQL indexes (there are n= o=20 table scans).
> >
> > The documentation for PostgreSQL 7.= 3=20 explains about introducing the
optimized PREPARE/EXECUTE statements, but= we=20 have discovered this is not
implemented (yet) in ECPG (we are using rele= ase=20 7.3.2). See comments in
"ecpg/preproc/preproc.y".
> >
> &= gt;=20 We have a major installation currently using Oracle, but they want to
sw= itch=20 to PostgreSQL when the access time reaches a reasonable level. The
size = of=20 database (tables + indexes) is about 120 GB, and the number of
SELECTs f= rom=20 the tables is of the magnitude 200 to 500 million per day,
split over a= =20 number of SUN E10K machines (about 30 processors each with 1 GB
RAM). (T= he=20 Oracle license and annual maintenance fee on this setup=20 are
exorbitant!!)
> >
> > Without the optimized=20 PREPARE/EXECUTE we see a SELECT time of about 30
milliseconds (measured = on=20 the SUN E10K) of which most seems to be CPU time
spent inside postmaster= . The=20 similar time from Oracle is about 1 to 5
milliseconds on the same hardwa= re.=20 Both PostgreSQL and Oracle have been
setup with a cache (shared_buffers)= of=20 120000.
> >
> > To our customer the 30 milliseconds are n= ot=20 acceptable. Based on the
PostgreSQL 7.3 documentation we should expect= =20 "improved performance" when
using prepared queries.
> >
>= >=20 This leads to a couple of questions regarding the=20 optimized
PREPARE/EXECUTE:
> >
> > - Do you have any= =20 indication of the performance improvement (measured in
percentage)?
&= gt;=20 >
> > - When will the optimized PREPARE/EXECUTE be implemented = in=20 ECPG?
> >
> >
> > Best Regards
> > Bo= =20 Tveden
> > Mads Madsen
> > BTS=20 Solutions
>
>
>
> ---------------------------(end o= f=20 broadcast)---------------------------
> TIP 4: Don't 'kill -9' the=20 postmaster
>
------=_NextPart_000_00A7_01C324F7.5EAFFDE0--