Hi Steve and other friends,
Some information you would be interested in:
I did some further tests using libpq in my code.
I used a stored proc to insert 100 thousand rows in a table, it took 25 sec
(almost same as time taken by Oracle PL/SQL and OCI interface).
Same inserts through libpq take 70 seconds.
I am inserting all records in a single transaction.
So, the problem seems to be optimization of usage of libpq in my code.
I am attaching my code below.
Is any optimization possible in this?
Do prepared statements help in cutting down the insert time to half for this
kind of inserts? One of the major problems with libpq usage is lack of good
documentation and examples.
I could not get any good example of prepared stmt usage anywhere.
//----------------------------------------------------------------------------------------------------------------------------
/*
* testlibpq.c
*
* Test the C version of libpq, the PostgreSQL frontend library.
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include "iostream.h"
#include "stdio.h"
#include <time.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields;
int i=0;
int howmany=0;
if (argc<2)
{
cout<<"please pass no of records as parameter"<<endl;
return -1;
}
sscanf(argv[1], "%d", &howmany);
cout<<"inserting "<<howmany<<" records"<<endl;
time_t mytime1 = time(0);
cout(&mytime1))<<endl;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and using
* environment variables or defaults for all other connection parameters.
*/
conninfo = "host=x.y.z.a dbname=xyz port=5432 user=sd password=fg" ;
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Our test case here involves using a cursor, for which we must be inside
* a transaction block. We could do the whole thing with a single
* PQexec() of "select * from pg_database", but that's too trivial to make
* a good example.
*/
/* Start a transaction block */
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* Should PQclear PGresult whenever it is no longer needed to avoid memory
* leaks
*/
PQclear(res);
char query[1024]={0};
for (; i<howmany;i++ )
{
sprintf (query, "INSERT INTO aaaa(a, b, c, d, e, f, g, h, j, k, l, m, n, p)
VALUES (67, 'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec-2010',
'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec-2010', 99999, 99999, %d,
9999,
'ABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRA')",
i);
res = PQexec(conn, query);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
cout<<"error at iteration "<<i<<":"<<PQresultErrorMessage(res)<<endl;
PQclear(res);
break;
}
//PQclear(res);
}
/* close the portal ... we don't bother to check for errors ... */
/*res = PQexec(conn, "CLOSE myportal");
PQclear(res);*/
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
cout<<i<<" records inserted!"<<endl;
mytime1 = time(0);
cout(&mytime1))<<endl;
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
//----------------------------------------------------------------------------------------------------------------------------
Best Regards,
Divakar
________________________________
From: Divakar Singh <
[email protected]>
To: Steve Singer <
[email protected]>
Cc:
[email protected];
[email protected]Sent: Tue, October 26, 2010 12:22:31 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
Answers:
How are you using libpq?
-Are you opening and closing the database connection between each insert?
[Need to check, will come back on this]
-Are you doing all of your inserts as one big transaction or are you doing a
transaction per insert
[Answer: for C++ program, one insert per transaction in PG as well as Oracle.
But in stored proc, I think both use only 1 transaction for all inserts]
-Are you using prepared statements for your inserts?
[Need to check, will come back on this]
-Are you using the COPY command to load your data or the INSERT command?
[No]
-Are you running your libpq program on the same server as postgresql?
[Yes]
-How is your libpq program connecting to postgresql, is it using ssl?
[No]
If your run "VACUUM VERBOSE tablename" on the table, what does it say?
[Need to check, will come back on this]
You also don't mention which version of postgresql your using.
[Latest, 9.x]
Best Regards,
Divakar
________________________________
From: Steve Singer <
[email protected]>
To: Divakar Singh <
[email protected]>
Cc:
[email protected];
[email protected]Sent: Tue, October 26, 2010 12:16:46 AM
Subject: Re: [PERFORM] Postgres insert performance and storage requirement
compared to Oracle
On 10-10-25 02:31 PM, Divakar Singh wrote:
My questions/scenarios are:
1. How does PostgreSQL perform when inserting data into an indexed
(type: btree)
table? Is it true that as you add the indexes on a table, the
performance
deteriorates significantly whereas Oracle does not show that much
performance
decrease. I have tried almost all postgreSQL performance tips
available. I want
to have very good "insert" performance (with indexes), "select"
performance is
not that important at this point of time.
-- Did you test?
Yes. the performance was comparable when using SQL procedure. However,
When I used libpq, PostgreSQL performed very bad. There was some
difference in environment also between these 2 tests, but I am assuming
libpq vs SQL was the real cause. Or it was something else?
So your saying that when you load the data with psql it loads fine, but
when you load it using libpq it takes much longer?
How are you using libpq?
-Are you opening and closing the database connection between each insert?
-Are you doing all of your inserts as one big transaction or are you
doing a transaction per insert
-Are you using prepared statements for your inserts?
-Are you using the COPY command to load your data or the INSERT command?
-Are you running your libpq program on the same server as postgresql?
-How is your libpq program connecting to postgresql, is it using ssl?
Some 10-12 columns ( like 2 timestamp, 4 int and 4 varchar), with 5
indexes on varchar and int fields including 1 implicit index coz of PK.
If your run "VACUUM VERBOSE tablename" on the table, what does it say?
You also don't mention which version of postgresql your using.
