performance libpq vs JDBC

От: Werner Scholtes
Тема: performance libpq vs JDBC
Дата: ,
Msg-id: 128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern
(см: обсуждение, исходный текст)
Ответы: Re: performance libpq vs JDBC  (Divakar Singh)
Список: pgsql-performance

Скрыть дерево обсуждения

performance libpq vs JDBC  (Werner Scholtes, )
 Re: performance libpq vs JDBC  (Divakar Singh, )
  Re: performance libpq vs JDBC  (Werner Scholtes, )
   Re: performance libpq vs JDBC  (Divakar Singh, )
    Re: performance libpq vs JDBC  (Werner Scholtes, )
     Re: performance libpq vs JDBC  (Divakar Singh, )
   Re: performance libpq vs JDBC  (Richard Huxton, )
    Re: performance libpq vs JDBC  (Werner Scholtes, )
     Re: performance libpq vs JDBC  (Richard Huxton, )
    Re: performance libpq vs JDBC  (Merlin Moncure, )

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 по дате сообщения:

От: Eric Comeau
Дата:
Сообщение: Re: How to get FK to use new index without restarting the database
От: Krzysztof Nienartowicz
Дата:
Сообщение: Re: Help with bulk read performance