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 09:48:36
Message-ID: 287475.44939.qm@web65412.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
Update and delete are the operations which affect more than 1 row in general.
The only thing is that the criteria has to be the same for all rows.
If you have different criteria for different rows in case of update or delete, 
you will have to fire 2 queries.

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

 Best Regards,
Divakar




________________________________
From: Werner Scholtes <Werner(dot)Scholtes(at)heuboe(dot)de>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>; "pgsql-performance(at)postgresql(dot)org" 
<pgsql-performance(at)postgresql(dot)org>
Sent: Thu, December 16, 2010 3:11:36 PM
Subject: Re: [PERFORM] performance libpq vs JDBC


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

 
Von:pgsql-performance-owner(at)postgresql(dot)org 
[mailto:pgsql-performance-owner(at)postgresql(dot)org] Im Auftrag von Divakar Singh
Gesendet: Donnerstag, 16. Dezember 2010 10:38
An: Werner Scholtes; pgsql-performance(at)postgresql(dot)org
Betreff: Re: [PERFORM] performance libpq vs JDBC
 
If you have all records before issuing Insert, you can do it like: insert into 
xxx values (a,b,c), (d,e,f), ......;
an example: http://kaiv.wordpress.com/2007/07/19/faster-insert-for-multiple-rows
 
Best Regards,
Divakar
 
 

________________________________

From:Werner Scholtes <Werner(dot)Scholtes(at)heuboe(dot)de>
To: Divakar Singh <dpsmails(at)yahoo(dot)com>; "pgsql-performance(at)postgresql(dot)org" 
<pgsql-performance(at)postgresql(dot)org>
Sent: Thu, December 16, 2010 2:51:53 PM
Subject: RE: [PERFORM] performance libpq vs JDBC



Unfortunately I cannot use COPY funtion, since I need the performance of JDBC 
for update 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 parameter 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 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 
sends 1 time 1000 rows, which is much more efficient.
 
I assume that the wire 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(at)yahoo(dot)com] 
Gesendet: Donnerstag, 16. Dezember 2010 09:11
An: Werner Scholtes; pgsql-performance(at)postgresql(dot)org
Betreff: 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,
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

pgsql-performance by date

Next:From: Eric ComeauDate: 2010-12-16 12:12:03
Subject: How to get FK to use new index without restarting the database
Previous:From: Werner ScholtesDate: 2010-12-16 09:41:36
Subject: Re: performance libpq vs JDBC

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