Skip site navigation (1) Skip section navigation (2)

Re: performance libpq vs JDBC

From: Divakar Singh <dpsmails(at)yahoo(dot)com>
To: Werner Scholtes <Werner(dot)Scholtes(at)heuboe(dot)de>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: performance libpq vs JDBC
Date: 2010-12-16 08:10:39
Message-ID: 209185.49892.qm@web65409.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
Can you trying writing libpq program using COPY functions?
I hope it will be better than prepared statements.

 Best Regards,
Divakar




________________________________
From: Werner Scholtes <Werner(dot)Scholtes(at)heuboe(dot)de>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)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 works as follows (pseudo code):
 
for( int loop = 0; loop < 1000; ++loop ) {
   PQexec("BEGIN");
   const char* sql = "INSERT INTO pg_perf_test (id, text) VALUES($1,$2)";
   PQprepare(m_conn,"stmtid",sql,0,NULL);
   for ( int i = 0; i < 1000; ++i ) 
      // Set values etc.
      PQexecPrepared(m_conn,…);
   }
   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 test program in Java using JDBC. It works as follows:
 
for( intloops = 0; loops < 1000; ++i) {
   String sql = "INSERT INTO pq_perf_test (id,text) VALUES (?,?)";
   PreparedStatement stmt = con.prepareStatement(sql);
   for(inti = 0; i < 1000; ++i ) {
      // Set values etc.
      stmt.addBatch();
   }
   stmt.executeBatch();
   con.commit();
   stmt.close();
}
 
I measured the duration of every loop of the outer 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. 

 
I need to enhance the performance of my 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 libpq.
 
Best regards,
Werner Scholtes


      

In response to

Responses

pgsql-performance by date

Next:From: Werner ScholtesDate: 2010-12-16 09:21:53
Subject: Re: performance libpq vs JDBC
Previous:From: Dan SchafferDate: 2010-12-15 20:15:14
Subject: Re: Help with bulk read performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group