Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 78ADF1337B8B for ; Thu, 16 Dec 2010 05:48:45 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 39718-07 for ; Thu, 16 Dec 2010 09:48:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm17.bullet.mail.ac4.yahoo.com (nm17.bullet.mail.ac4.yahoo.com [98.139.52.214]) by mail.postgresql.org (Postfix) with SMTP id 779801337990 for ; Thu, 16 Dec 2010 05:48:37 -0400 (AST) Received: from [98.139.52.192] by nm17.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:48:37 -0000 Received: from [98.139.52.158] by tm5.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:48:37 -0000 Received: from [127.0.0.1] by omp1041.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:48:37 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 514808.79257.bm@omp1041.mail.ac4.yahoo.com Received: (qmail 76317 invoked by uid 60001); 16 Dec 2010 09:48:37 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1292492917; bh=BgjNTiYwXebwWlxdXpiGD3OXJZwlwQzOHhRR0tjTg6o=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=CsbY1kf/OEEuz5Vw4UNzK/EqGFCZJNCYu0RMys7Lv+oTS1VMETkGjDdX7rUlQAObRale/GuSSSPl6OwCwg1TwqMy+Of30VlV5s+b419i4F0r4XAepgxqyvpFVMC256rftw/DDHtUYCWlnxSwwFjvrg3KBDlK0alA9d/+42jW/5Q= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=N2Im0wwyBhb4qpoSeFGZSdaEP4jpjM483KR5Bed1c7YsrYBvEoNj4oLyUaAq9JylY1kMsJt3nJIurWeFQEk3loP86zGemJ4Onb7y/V3LxLpmMUOiI1u1cIJsHsnN6ycAzMvvwchLTUTd4uaTKLCZ0A0xmCcoACsmflpJbWaoY7s=; Message-ID: <287475.44939.qm@web65412.mail.ac4.yahoo.com> X-YMail-OSG: kUCTVmgVM1mHsQQucy_mqNbW4cQKB6iHa0U7Yo6I.yN5lpI uwxpJV8hApzp9dCWaQxOVp5QscLGKnEGlScHDvbroanzsIbM2Fz7HwSWhRJk Kc4uoK_4x2BCQZOY1q03onCGS0OvR0LQ5iqh6fbW4n64iF.YuUBIWGtfRdl7 ISUmEHRmutuwvN8KR5ZqFaTooIXU2T8JyuqeNXTPZOuR_xmBo_Duz14xMmzd bS60BLyPXLKREKqRP2MOOWsFnCpArvV21uo8V5ttJoz20MsfXlDUtGQxV37w oyOUna506DAWTL7J7MNrjwGfQIGQRF1pAt72hcoCvFU7FE8tiRBqjlbK4Ui6 C.mHahAwYf5OTs6NhXzW0SnJPOyODLO81vU1w2HvJrnqHikT2pIdJTMtA9BV 2Umj.T_buImJk Received: from [194.237.142.20] by web65412.mail.ac4.yahoo.com via HTTP; Thu, 16 Dec 2010 01:48:36 PST X-Mailer: YahooMailRC/553 YahooMailWebService/0.8.107.285259 References: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern> <209185.49892.qm@web65409.mail.ac4.yahoo.com> <128DB53227227D43B4AAEED73E89A81901E2C4B58B@vm-exchg-2k8.heuboe.hbintern> <828784.32976.qm@web65413.mail.ac4.yahoo.com> <128DB53227227D43B4AAEED73E89A81901E2C4B58C@vm-exchg-2k8.heuboe.hbintern> Date: Thu, 16 Dec 2010 01:48:36 -0800 (PST) From: Divakar Singh Subject: Re: performance libpq vs JDBC To: Werner Scholtes , "pgsql-performance@postgresql.org" In-Reply-To: <128DB53227227D43B4AAEED73E89A81901E2C4B58C@vm-exchg-2k8.heuboe.hbintern> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-324207969-1292492916=:44939" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.907 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/188 X-Sequence-Number: 41640 --0-324207969-1292492916=:44939 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Update and delete are the operations which affect more than 1 row in genera= l.=0AThe only thing is that the criteria has to be the same for all rows.= =0AIf you have different criteria for different rows in case of update or d= elete, =0Ayou will have to fire 2 queries.=0A=0AI mean, if you want to do= =0A1. delete from xyz where a =3D 1=0Aand=0A2. delete from xyz where a =3D = 2=0AThen you will have to run query 2 times.=0A=0A Best Regards,=0ADivakar= =0A=0A=0A=0A=0A________________________________=0AFrom: Werner Scholtes =0ATo: Divakar Singh ; "pgsql-p= erformance@postgresql.org" =0A=0ASent: Th= u, December 16, 2010 3:11:36 PM=0ASubject: Re: [PERFORM] performance libpq = vs JDBC=0A=0A=0AWhat about update and delete? In case of an update I have a= ll records to be =0Aupdated and in case of an delete I have all primary key= values of records to be =0Adeleted. =0A=0A =0AVon:pgsql-performance-owner@= postgresql.org =0A[mailto:pgsql-performance-owner@postgresql.org] Im Auftra= g von Divakar Singh=0AGesendet: Donnerstag, 16. Dezember 2010 10:38=0AAn: W= erner Scholtes; pgsql-performance@postgresql.org=0ABetreff: Re: [PERFORM] p= erformance libpq vs JDBC=0A =0AIf you have all records before issuing Inser= t, you can do it like: insert into =0Axxx values (a,b,c), (d,e,f), ......;= =0Aan example: http://kaiv.wordpress.com/2007/07/19/faster-insert-for-multi= ple-rows=0A =0ABest Regards,=0ADivakar=0A =0A =0A=0A_______________________= _________=0A=0AFrom:Werner Scholtes =0ATo: Divak= ar Singh ; "pgsql-performance@postgresql.org" =0A=0ASent: Thu, December 16, 2010 2:51:53 PM=0ASu= bject: RE: [PERFORM] performance libpq vs JDBC=0A=0A=0A=0AUnfortunately I c= annot use COPY funtion, since I need the performance of JDBC =0Afor update = and delete statements in C++ libpq-program as well.=0A =0AI wonder how JDBC= PreparedStatement.addBatch() and =0APreparedStatement.executeBatch() work= . They need to have a more efficient =0Aprotocol to send bulks of parameter= sets for one prepared statement as batch in =0Aone network transmission to= the server. As far as I could see PQexecPrepared =0Adoes not allow to send= more than one parameter set (parameters for one row) in =0Aone call. So li= bpq sends 1000 times one single row to the server where JDBC =0Asends 1 tim= e 1000 rows, which is much more efficient.=0A =0AI assume that the wire pro= tocol of PostgreSQL allows to transmit multiple rows =0Aat once, but libpq = doesn't have an interface to access it. Is that right? =0A=0A =0AVon:Divaka= r Singh [mailto:dpsmails@yahoo.com] =0AGesendet: Donnerstag, 16. Dezember 2= 010 09:11=0AAn: Werner Scholtes; pgsql-performance@postgresql.org=0ABetreff= : Re: [PERFORM] performance libpq vs JDBC=0A =0ACan you trying writing libp= q program using COPY functions?=0AI hope it will be better than prepared st= atements.=0A =0ABest Regards,=0ADivakar=0A =0A =0A=0A______________________= __________=0A=0AFrom:Werner Scholtes =0ATo: "pgs= ql-performance@postgresql.org" =0ASent: W= ed, December 15, 2010 8:21:55 PM=0ASubject: [PERFORM] performance libpq vs = JDBC=0A=0A=0AI wrote a test program in C++ using libpq. It works as follows= (pseudo code):=0A =0Afor( int loop =3D 0; loop < 1000; ++loop ) {=0A PQe= xec("BEGIN");=0A const char* sql =3D "INSERT INTO pg_perf_test (id, text)= VALUES($1,$2)";=0A PQprepare(m_conn,"stmtid",sql,0,NULL);=0A for ( int= i =3D 0; i < 1000; ++i ) =0A // Set values etc.=0A PQexecPrepare= d(m_conn,=E2=80=A6);=0A }=0A PQexec("DEALLOCATE stmtid");=0A PQexec("= COMMIT"); =0A}=0A =0AI measured the duration of every loop of the outer f= or-loop resulting in an =0Aaverage of 450 ms (per 1000 data sets insert)=0A= =0AAfter that, I wrote a test program in Java using JDBC. It works as foll= ows:=0A =0Afor( intloops =3D 0; loops < 1000; ++i) {=0A String sql =3D "I= NSERT INTO pq_perf_test (id,text) VALUES (?,?)";=0A PreparedStatement stm= t =3D con.prepareStatement(sql);=0A for(inti =3D 0; i < 1000; ++i ) {=0A = // Set values etc.=0A stmt.addBatch();=0A }=0A stmt.executeBa= tch();=0A con.commit();=0A stmt.close();=0A}=0A =0AI measured the durat= ion of every loop of the outer for-loop resulting in an =0Aaverage of 100 m= s (per 1000 data sets insert)=0A =0AThis means that accessing PostgreSQL by= JDBC is about 4-5 times faster than =0Ausing libpq. =0A=0A =0AComparable = results have been measured with analog update and delete statements. =0A=0A= =0AI need to enhance the performance of my C++ code. Is there any possibil= ity in =0Alibpq to reach the performance of JDBC for INSERT, UPDATE and DEL= ETE statements =0A(I have no chance to use COPY statements)? I didn't find = anything comparable to =0APreparedStatement.executeBatch() in libpq.=0A =0A= Best regards,=0AWerner Scholtes=0A=0A=0A --0-324207969-1292492916=:44939 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Update and delete are the operations which affect more than 1 row in gen= eral.
The only thing is that the criteria has to be the same for all row= s.
If you have different criteria for different rows in case of update o= r delete, you will have to fire 2 queries.

I mean, if you want to do=
1. delete from xyz where a =3D 1
and
2. delete from xyz where a = =3D 2
Then you will have to run query 2 times.

 Best Regards= ,
Divakar


From: Werner Sch= oltes <Werner.Scholtes@heuboe.de>
To: Divakar Singh <dpsmails@yahoo.com>; "pgsql-per= formance@postgresql.org" <pgsql-performance@postgresql.org>
Sent: Thu, December 16, 2010 3:1= 1:36 PM
Subject: Re: [P= ERFORM] performance libpq vs JDBC

What about = update and delete? In case of an update I have all records to be updated an= d in case of an delete I have all primary key values of records to be delet= ed.

 

Von: pgsql-performance-owner@postgresql.org [mailto:p= gsql-performance-owner@postgresql.org] Im Auftrag von Divakar Singh<= br>Gesendet: Donnerstag, 16. Dezember 2010 10:38
An: Werne= r Scholtes; pgsql-performance@postgresql.org
Betreff: Re: [PERFOR= M] performance libpq vs JDBC

=  

If you have all records before issuing Insert, y= ou can do it like: insert into xxx values (a,b,c), (d,e,f), ......;
an e= xample: http://kaiv.wordpress.com/= 2007/07/19/faster-insert-for-multiple-rows

 

Best Regards,
Divakar

 

 

From: Werner Scholtes <Werner.Scholtes@heuboe.de>
= To: Divakar Singh <dpsmails@yahoo.com>; "pgsql-performance@pos= tgresql.org" <pgsql-performance@postgresql.org>
Sent: Thu, = December 16, 2010 2:51:53 PM
Subject: RE: [PERFORM] performance l= ibpq vs JDBC


Unfortunately I cannot use COPY funtion, since I need the performan= ce of JDBC for update and delete statements in C++ libpq-program as well.

&n= bsp;

I wonder how JDBC  PreparedStatement.addBatch() and PreparedStatem= ent.executeBatch() work. They need to have a more efficient protocol to sen= d bulks of parameter sets for one prepared statement as batch in one networ= k transmission to the server. As far as I could see PQexecPrepared does not= allow to send more than one parameter set (parameters for one row) in one = call. So libpq sends 1000 times one single row to the server where JDBC sen= ds 1 time 1000 rows, which is much more efficient.

 

I assume that the w= ire protocol of PostgreSQL allows to transmit multiple rows at once, but libpq= doesn't have an interface to access it. Is that right?

 

Von: Divakar Singh [mailto:dpsmails@ya= hoo.com]
Gesendet: Donnerstag, 16. Dezember 2010 09:11
An:= Werner Scholtes; pgsql-performance@postgresql.org
Betreff: R= e: [PERFORM] performance libpq vs JDBC

 

Can you trying writing libpq program using COPY functions?
I hope it will be be= tter than prepared statements.

 

Best Regards,
Divakar

 

=

 <= /p>

From: Werner Scholtes <Werner.Scholtes@heuboe.de>
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org&g= t;
Sent: Wed, December 15, 2010 8:21:55 PM
Subject: [PE= RFORM] performance libpq vs JDBC

I wrote a test program = in C++ using libpq. It works as follows (pseudo code):

 

for ( int loop =3D 0; loop < 1000; ++loop ) {

 &nb= sp; PQexec("BEGIN");

 &n= bsp; const char* sql =3D "INSERT INTO pg_perf_test (id, text) VALUES($1,$2)";

   "stmtid= ",sql,0,NULL);

  = ; for ( in= t i =3D 0; i < 1000; ++i )

    =   // Set values etc.

  = ;    PQexecPrepared(m_conn,=E2=80=A6);

   }

 &nbs= p; PQexec("DEALLOCATE stmtid");

   PQexec("COMMIT"= );  

}

 

I measured the duration of every loop of the outer for= -loop resulting in an average of 450 ms (per 1000 data sets insert)

 

After that, I wrote a t= est program in Java using JDBC. It works as follows:

 

for<= span style=3D"font-size: 10pt; font-family: "Courier New"; color:= black;"> ( int loops = =3D 0; loops < 1000; ++i) {

&= nbsp;  String sql =3D "INSERT INTO pq_perf_test (id,text) VA= LUES (?,?)";

&= nbsp;  PreparedStatement stmt =3D con= .prepareStatement(sql);

for (int i =3D 0; i < 1000; ++i ) {

=

      // Set values etc.

&= nbsp;     stmt.addBatch();

   }

&= nbsp;  stmt.executeBatch();

   con.commit();

   stmt.close();

}

 

I measured the duration of every loop of the outer for-loop resu= lting in an average of 100 ms (per 1000 data sets insert)

 

This means that accessing Postgre= SQL by JDBC is about 4-5 times faster than using libpq.

 

Comparable  results have been= measured with analog update and delete statements.

 

I need to enhance the performance of m= y C++ code. Is there any possibility in libpq to reach the performance of JDBC for INSERT= , UPDATE and DELETE statements (I have no chance to use COPY statements)? I= didn't find anything comparable to PreparedStatement.executeBatch() in lib= pq.

 

Best regards= ,

Werner Scholtes

     

 

 

 

 

 

=0A
<= br>=0A=0A --0-324207969-1292492916=:44939--