From: Divakar Singh <dpsmails@yahoo.com>
To: Werner Scholtes <Werner.Scholtes@heuboe.de>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: performance libpq vs JDBC
Date: Thu, 16 Dec 2010 00:10:39 -0800 (PST)
Message-ID: <209185.49892.qm@web65409.mail.ac4.yahoo.com> (raw)
In-Reply-To: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern>
References: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern>
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( 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dpsmails@yahoo.com, Werner.Scholtes@heuboe.de
Subject: Re: performance libpq vs JDBC
In-Reply-To: <209185.49892.qm@web65409.mail.ac4.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox