From: Steve Singer <ssinger@ca.afilias.info>
To: Divakar Singh <dpsmails@yahoo.com>
Cc: jd@commandprompt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Date: Wed, 27 Oct 2010 11:42:09 -0400
Message-ID: <4CC84851.3070100@ca.afilias.info> (raw)
In-Reply-To: <132275.30531.qm@web65411.mail.ac4.yahoo.com>
References: <132275.30531.qm@web65411.mail.ac4.yahoo.com>
On 10-10-27 10:00 AM, Divakar Singh wrote:
> 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.
Yes using prepared statements should make this go faster, but your best
bet might be to use the COPY command. I don't have a PQprepare example
handy though we probably should add one to the docs.
The copy command would be used similar to
PQexec(conn,"COPY TO aaaa (a,b,c,d,e,f,g,h,j,k,l,m,n,p) FROM STDIN WITH
(DELIMITER ',') ");
for(; i < howmany;i++)
{
sprintf(query,"67,'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec
2010', 'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec-2010',
99999, 99999, %d, 9999,
'ABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRA'\n",
i);
res = PQputCopyData(conn,query,strlen(query);
}
PQputCopyEnd(conn,NULL);
I have not actually tried the above code snippet, it is just to give you
the general idea.
You call PQexec with the COPY command outside the loop then at each loop
iteration you call PQputCopyData with some of the data that gets passed
to the server.
You can combine multiple lines on a single PQputCopyData call if you want.
http://www.postgresql.org/docs/9.0/interactive/sql-copy.htmlhttp://www.postgresql.org/docs/9.0/interactive/libpq-copy.html>
>
> 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);*/
<snip>
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: ssinger@ca.afilias.info, dpsmails@yahoo.com, jd@commandprompt.com
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
In-Reply-To: <4CC84851.3070100@ca.afilias.info>
* 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