Re: performance libpq vs JDBC - Mailing list pgsql-performance

From Werner Scholtes
Subject Re: performance libpq vs JDBC
Date
Msg-id 128DB53227227D43B4AAEED73E89A81901E2C4B58B@vm-exchg-2k8.heuboe.hbintern
Whole thread Raw
In response to Re: performance libpq vs JDBC  (Divakar Singh <dpsmails@yahoo.com>)
Responses Re: performance libpq vs JDBC  (Divakar Singh <dpsmails@yahoo.com>)
Re: performance libpq vs JDBC  (Richard Huxton <dev@archonet.com>)
List pgsql-performance

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@yahoo.com]
Gesendet: Donnerstag, 16. Dezember 2010 09:11
An: Werner Scholtes; pgsql-performance@postgresql.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.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 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 ( int loops = 0; loops < 1000; ++i) {

   String sql = "INSERT INTO pq_perf_test (id,text) VALUES (?,?)";

   PreparedStatement stmt = con.prepareStatement(sql);

   for (int i = 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

     

 

 

 

 

pgsql-performance by date:

Previous
From: Divakar Singh
Date:
Subject: Re: performance libpq vs JDBC
Next
From: Divakar Singh
Date:
Subject: Re: performance libpq vs JDBC