Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 5B5A21337B53 for ; Thu, 16 Dec 2010 04:10:50 -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 86541-06 for ; Thu, 16 Dec 2010 08:10:42 +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 BEC7C1337B2F for ; Thu, 16 Dec 2010 04:10:41 -0400 (AST) Received: from [98.139.52.191] by nm17.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 08:10:40 -0000 Received: from [98.139.52.136] by tm4.bullet.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 08:10:40 -0000 Received: from [127.0.0.1] by omp1019.mail.ac4.yahoo.com with NNFMP; 16 Dec 2010 08:10:40 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 384377.96707.bm@omp1019.mail.ac4.yahoo.com Received: (qmail 49900 invoked by uid 60001); 16 Dec 2010 08:10:40 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1292487040; bh=5JvP1i4Tdl2/mDuzd/e6900bgGcwUKPhGL+fLvfplaE=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:References:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=cUgXgzRq31A6ifWgozleOZik+VvznWRtGdB8kHJwFil0J3+fkY4v2GooPj1/Ns0qfdeTTybGquwE9GcAOoHe8mcJeTnjEvy+0P5EwC90H7LwBTqkZ3Gym2QFVMbmsTZyp225CpXOaLPQRMxrYseT/NgeJvlvtgGa53ajDa2MqiM= 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=frWV2T304DzQ4qatSSxfq96kHYj4K+xlDMmfiIeIvXRd2wG/4pdErg/HOBwZawdXphIbZuIk1P55HFUrXADX9eb4N+BlnMGY+1Ifu+cfIuqg1lg1WOfQDBdSzve0swZhAuUaURfbXzLQlSuXdPw3FMRdFwiX+nIqFDnrvwDFn98=; Message-ID: <209185.49892.qm@web65409.mail.ac4.yahoo.com> X-YMail-OSG: 8WbHwXsVM1nsiubVIUFQR910Cf3RHL6zO6kiYlqZIxfP56c UvQfE2zzRttJK0p3u1GrFCuysZikOm.udR.3qkomG_atJ6nUkHbodUUGKESP nAjaV9T5e498B_8ux1iyjV41WHiEBJdDED6bMBjLQcN.YpLAn.frHxPBq0mu csryRO4.tGRT8xLdrozfEoTjkG8c2OQf_ZcWz.X_YC7uMatbwz.1.Avx8nm2 XMdRRipEfLNycayn55pGZZ3p9GGReRH4LXWQ.6jdNe6V63rUdrJqXV8twSeN wI0wIGu5lRmenRT8xQkUvIbPRUWppxe6kcNIaprfm1iBhd_Ea8xI_Rp52I.r .EjMqQabre5xmvloP1xMBIKVsCkPbD4JMYqc5Ui.RaotMmeskBRFCzWSJzZV zrtzbqsHlf4La Received: from [194.237.142.20] by web65409.mail.ac4.yahoo.com via HTTP; Thu, 16 Dec 2010 00:10:39 PST X-Mailer: YahooMailRC/553 YahooMailWebService/0.8.107.285259 References: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern> Date: Thu, 16 Dec 2010 00:10:39 -0800 (PST) From: Divakar Singh Subject: Re: performance libpq vs JDBC To: Werner Scholtes , "pgsql-performance@postgresql.org" In-Reply-To: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-436428184-1292487039=:49892" 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/184 X-Sequence-Number: 41636 --0-436428184-1292487039=:49892 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Can you trying writing libpq program using COPY functions?=0AI hope it will= be better than prepared statements.=0A=0A Best Regards,=0ADivakar=0A=0A=0A= =0A=0A________________________________=0AFrom: Werner Scholtes =0ATo: "pgsql-performance@postgresql.org" =0ASent: Wed, December 15, 2010 8:21:55 PM=0ASubject: [PERF= ORM] 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 PQexec("BEGIN");=0A const char* sql =3D "INSERT IN= TO pg_perf_test (id, text) VALUES($1,$2)";=0A PQprepare(m_conn,"stmtid",s= ql,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("DEALLOCA= TE stmtid");=0A PQexec("COMMIT"); =0A}=0A =0AI measured 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 us= ing 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.prepareStatement(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 the outer for-loop resulti= ng in an =0Aaverage of 100 ms (per 1000 data sets insert)=0A =0AThis means = that accessing PostgreSQL by JDBC is about 4-5 times faster than =0Ausing l= ibpq. =0A=0A =0AComparable results have been measured with analog update a= nd delete statements. =0A=0A =0AI need to enhance the performance of my C++= code. Is there any possibility in =0Alibpq to reach the performance of JDB= C for INSERT, UPDATE and DELETE statements =0A(I have no chance to use COPY= statements)? I didn't find anything comparable to =0APreparedStatement.exe= cuteBatch() in libpq.=0A =0ABest regards,=0AWerner Scholtes=0A=0A=0A --0-436428184-1292487039=:49892 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Can you trying writing libpq program using COPY functions?
I hope it = will be better than prepared statements.
 
Best Regards,<= br>Divakar


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

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

 

for ( int <= span style=3D"color: rgb(1, 0, 1);">loop =3D 0; loop < 1000; ++loop ) {

   PQexec("BEGIN");

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

   PQpr= epare(m_conn, "stmtid",sql,0,NULL);

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

 &nb= sp;    // Set values etc.=

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

 =   }

   PQexec("DEALLO= CATE stmtid");

   PQexec("COMMIT");  

}=

 

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

 

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

 

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

   String sql =3D <= /span>"INSERT INTO pq_perf_test (id,text) VALUES (?,?)"= ;

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

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

      // Set values etc.

      stmt.addBatch= ();

   }

   stmt.executeBatch();

 &nbs= p; con.commit();

   stmt.close();

}

 =

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

 

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

 

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

<= p class=3D"MsoNormal">  

I need to enhance t= he performance of my C++ code. Is there any possibility in libpq to reach t= he 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 libpq.

&nbs= p;

Best regards,

Werner= Scholtes

 <= /p>

 

 

= =0A

=0A=0A --0-436428184-1292487039=:49892--