Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 68A1E1337990 for ; Thu, 16 Dec 2010 05:37:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 74166-10 for ; Thu, 16 Dec 2010 09:37:35 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm26.bullet.mail.ac4.yahoo.com (nm26.bullet.mail.ac4.yahoo.com [98.139.52.223]) by mail.postgresql.org (Postfix) with SMTP id 723D71336FA7 for ; Thu, 16 Dec 2010 05:37:33 -0400 (AST) Received: from [98.139.52.194] by nm26.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:37:33 -0000 Received: from [98.139.52.143] by tm7.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:37:33 -0000 Received: from [127.0.0.1] by omp1026.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 09:37:33 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 8159.78700.bm@omp1026.mail.ac4.yahoo.com Received: (qmail 33248 invoked by uid 60001); 16 Dec 2010 09:37:32 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1292492252; bh=S1MumOGYVA7LN4ynd3di+6BCKCHDMtPWXMYV0Xa17O0=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=pryedYvwzis3j5gtOivbFG47d1O14F3eMgsvYgDPTTnTtssHR6VPQLLK3Vxi7WybnTfOR+9zch8p7GOHOaIW4XUKgiW45G4Tab9l25BbUfo0fvIgMOgaed/05Hh4c8vmoeZsqeI50at+Iqw60tq1PSkYqFMLpcd1ZbfsipYzNuk= 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=dxPzuXHwWfpfRDJAUsoHW6rP+O744ovnhjl05PMaD6j4Nlnf4JH1oi5aWEeQQ0Q73veStpp527t4f9BW62dI/2DuwQNSpsatZszIi+aY77tmFutfx9gHbtQylnVbtW5Z9h9jCWIpSQ1kn4kP8+jZ4r4I/ocUm6rg/LPYlSvPsY0=; Message-ID: <828784.32976.qm@web65413.mail.ac4.yahoo.com> X-YMail-OSG: 8_.FAWoVM1nY3bgIJ5IDucfeici8KrBe6HNhtWs39CGnJid 3C6.IgNiAFe5LhpnNTnACBnRTfH7ZbefZmcjPQaGX4ZN6IqIcSyMDvvwtMT5 Eye3eQyVs45eIkPfUlB8GG3zBZs1D.pSnlFRvY0Vyq.w92_7H4VN0dtjUOuF Xy1P.owLkpqgrZmrteE.v59wTqif3sigcBEAZE.Y_zi8Vaw4QicAlsCks0rK 69T1kccaakqT.Xt3FmVqppCA7PoBFpe__Iw6XUSbwchep6Rx8_mHGutQkkYY aky78.vLXyaJ5nQKl0Ibuc41IpHX3vf2jzPDO4SP2M08iKA53czqg.IryYCN oQdJQBRFyB2or6nxdvup1sVh7KSiwTEfWHpbOE4iYimb.So4ZfSnexxyAQg5 TaUl2v7YbELqL Received: from [194.237.142.20] by web65413.mail.ac4.yahoo.com via HTTP; Thu, 16 Dec 2010 01:37:32 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> Date: Thu, 16 Dec 2010 01:37:32 -0800 (PST) From: Divakar Singh Subject: Re: performance libpq vs JDBC To: Werner Scholtes , "pgsql-performance@postgresql.org" In-Reply-To: <128DB53227227D43B4AAEED73E89A81901E2C4B58B@vm-exchg-2k8.heuboe.hbintern> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-67714997-1292492252=:32976" 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/186 X-Sequence-Number: 41638 --0-67714997-1292492252=:32976 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable If you have all records before issuing Insert, you can do it like: insert i= nto =0Axxx values (a,b,c), (d,e,f), ......;=0Aan example: http://kaiv.wordp= ress.com/2007/07/19/faster-insert-for-multiple-rows=0A=0A=0A Best Regards,= =0ADivakar=0A=0A=0A=0A=0A________________________________=0AFrom: Werner Sc= holtes =0ATo: Divakar Singh = ; "pgsql-performance@postgresql.org" =0A= =0ASent: Thu, December 16, 2010 2:51:53 PM=0ASubject: RE: [PERFORM] perform= ance libpq vs JDBC=0A=0A=0AUnfortunately I cannot use COPY funtion, since I= need the performance of JDBC =0Afor update and delete statements in C++ li= bpq-program as well.=0A =0AI wonder how JDBC PreparedStatement.addBatch() = and =0APreparedStatement.executeBatch() work. They need to have a more effi= cient =0Aprotocol to send bulks of parameter sets for one prepared statemen= t 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 (p= arameters for one row) in =0Aone call. So libpq sends 1000 times one single= row to the server where JDBC =0Asends 1 time 1000 rows, which is much more= efficient.=0A =0AI assume that the wire protocol of PostgreSQL allows to t= ransmit multiple rows =0Aat once, but libpq doesn't have an interface to ac= cess it. Is that right? =0A=0A =0AVon:Divakar Singh [mailto:dpsmails@yahoo.= com] =0AGesendet: Donnerstag, 16. Dezember 2010 09:11=0AAn: Werner Scholtes= ; pgsql-performance@postgresql.org=0ABetreff: Re: [PERFORM] performance lib= pq vs JDBC=0A =0ACan you trying writing libpq program using COPY functions?= =0AI hope it will be better than prepared statements.=0A =0ABest Regards,= =0ADivakar=0A =0A =0A=0A________________________________=0A=0AFrom:Werner S= choltes =0ATo: "pgsql-performance@postgresql.org= " =0ASent: Wed, December 15, 2010 8:21:55= PM=0ASubject: [PERFORM] performance libpq vs JDBC=0A=0A=0A=0AI wrote a tes= t program in C++ using libpq. It works as follows (pseudo code):=0A =0Afor(= int loop =3D 0; loop < 1000; ++loop ) {=0A PQexec("BEGIN");=0A const c= har* sql =3D "INSERT INTO pg_perf_test (id, text) VALUES($1,$2)";=0A PQpr= epare(m_conn,"stmtid",sql,0,NULL);=0A for ( int i =3D 0; i < 1000; ++i ) = =0A // Set values etc.=0A PQexecPrepared(m_conn,=E2=80=A6);=0A = }=0A PQexec("DEALLOCATE stmtid");=0A PQexec("COMMIT"); =0A}=0A =0AI m= easured the duration of every loop of the outer for-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 follows:=0A =0Afor( intloops= =3D 0; loops < 1000; ++i) {=0A String sql =3D "INSERT INTO pq_perf_test = (id,text) VALUES (?,?)";=0A PreparedStatement stmt =3D con.prepareStateme= nt(sql);=0A for(inti =3D 0; i < 1000; ++i ) {=0A // Set values etc.= =0A stmt.addBatch();=0A }=0A stmt.executeBatch();=0A con.commit(= );=0A stmt.close();=0A}=0A =0AI measured the duration of every loop of th= e outer for-loop resulting in an =0Aaverage of 100 ms (per 1000 data sets i= nsert)=0A =0AThis means that accessing PostgreSQL by JDBC is about 4-5 time= s faster than =0Ausing libpq. =0A=0A =0AComparable results have been measu= red with analog update and delete statements. =0A=0A =0AI need to enhance t= he performance of my C++ code. Is there any possibility in =0Alibpq to reac= h the performance of JDBC for INSERT, UPDATE and DELETE statements =0A(I ha= ve no chance to use COPY statements)? I didn't find anything comparable to = =0APreparedStatement.executeBatch() in libpq.=0A =0ABest regards,=0AWerner = Scholtes=0A=0A=0A --0-67714997-1292492252=:32976 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
If you have all records before issuing Insert, you can do it like: inser= t into xxx values (a,b,c), (d,e,f), ......;
an example: http://kaiv.wordpress.com/2007/07/19/faster-insert-for-multi= ple-rows

 
Best Regards,
Divakar

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

Unfortunate= ly I cannot use COPY funtion, since I need the performance of JDBC for upda= te and delete statements in C++ libpq-program as well.

 

I wonder how = JDBC  PreparedStatement.addBatch() and PreparedStatement.executeBatch(= ) work. They need to have a more efficient protocol to send bulks of parame= ter sets for one prepared statement as batch in one network transmission to= the server. As far as I could see PQexecPrepared does not allow to send mo= re than one parameter set (parameters for one row) in one call. So libpq se= nds 1000 times one single row to the server where JDBC sends 1 time 1000 ro= ws, which is much more efficient.

 <= /span>

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

 

Von: Divakar Singh [mailto:dps= mails@yahoo.com]
Gesendet: Donnerstag, 16. Dezember 2010 09:11An: Werner Scholtes; pgsql-performance@postgresql.org
Betref= f: Re: [PERFORM] performance libpq vs JDBC

 

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

 

Best Regards,
Divaka= r

&= nbsp;

 

From: Werner Scholtes <Werner.Scholtes@heuboe.d= e>
To: "pgsql-performance@postgresql.org" <pgsql-performanc= e@postgresql.org>
Sent: Wed, December 15, 2010 8:21:55 PM
<= b>Subject: [PERFORM] performance libpq vs JDBC


=

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

 

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

   PQexec("BEGIN"= );

   c= onst char* sql =3D "IN= SERT INTO pg_perf_test (id, text) VALUES($1,$2)";

   PQprepare(m_conn, "stmtid",sql,0,NULL);

   <= span style=3D"color: blue;">for ( int i =3D 0; i < 1000; ++i )

     = ; // Set values etc.

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

   }

   P= Qexec("DEALLOCATE stmtid");

&= nbsp;  PQexec("COMMIT");&n= bsp; 

}

 

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

 

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

 

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

   String sql =3D "INSER= T INTO pq_perf_test (id,text) VALUES (?,?)";

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

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

     = // Set values etc.

      stmt.addBatch();

   }

   stmt.executeBatch();

   con.commit();

   stmt.close();<= /p>

}

 <= /p>

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

 

This means that ac= cessing PostgreSQL by JDBC is about 4-5 times faster than using libpq.

=

 

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

 

I need to enhance the p= erformance of my C++ code. Is there any possibility in libpq to reach the p= erformance of JDBC for INSERT, UPDATE and DELETE statements (I have no chan= ce to use COPY statements)? I didn't find anything comparable to PreparedSt= atement.executeBatch() in libpq.

 

Best regards,

Werner Scholte= s

   = ;  

 

 

 

 

=0A
=0A=0A=0A=0A=0A=0A=0A=0A = --0-67714997-1292492252=:32976--