On Wed, Oct 27, 2010 at 08:00, Divakar Singh <dpsmails@xxxxxxxxx> wrote:
> 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?
In half? not for me. Optimization possible? Sure, using the code you
pasted (time ./a.out 100000 <method>):
PQexec: 41s
PQexecPrepared: 36s
1 insert statement: 7s
COPY: 1s
psql: 256ms
Basically the above echoes the suggestions of others, use COPY if you can.
Find the source for the above attached. Its just a very quick
modified version of what you posted. [ disclaimer the additions I
added are almost certainly missing some required error checking... ]
[ psql is fast because the insert is really dumb: insert into aaaa (a,
b, c, d, e, f, g, h, j, k, l, m, n, p) select 1, 'asdf', 'asdf',
'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf',
'asdf', 'asdf', 'asdf' from generate_series(1, 100000); ]
//----------------------------------------------------------------------------------------------------------------------------
/*
* testlibpq.c
*
* Test the C version of libpq, the PostgreSQL frontend library.
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <iostream>
using namespace std;
#include <string.h>
#include <stdio.h>
#include <time.h>
PGconn *conn;
int howmany=0;
static void
exit_nicely(void)
{
PQfinish(conn);
exit(1);
}
static void
pgexec(void)
{
printf("using PQexec\n");
char query[1024]={0};
for(int i=0; i<howmany;i++)
{
PGresult *res;
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);
}
}
static void
pgprepare(void)
{
printf("using PQprepare\n");
PGresult *res = PQprepare(conn, "my_insert", "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, $1, 9999, 'ABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRA')", 1, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "insert command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely();
}
PQclear(res);
char **vals = (char**)calloc(1, sizeof(char *));
if(!vals)
abort();
vals[0] = (char*)calloc(10, sizeof(char));
if(!vals[0])
abort();
for (int i=0; i<howmany;i++)
{
sprintf(vals[0], "%d", i);
res = PQexecPrepared(conn, "my_insert", 1, vals, NULL, NULL, 0);
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
cout<<"error at iteration "<<i<<":"<<PQresultErrorMessage(res)<<endl;
PQclear(res);
break;
}
PQclear(res);
}
free(vals[0]);
free(vals);
}
static void
pginsert(void)
{
printf("using one insert command\n");
const char *line = " (67, 'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec-2010', 'ABRAKADABRA', 'ABRAKADABRA', 'ABRAKADABRA', '1-Dec-2010', 99999, 99999, %d, 9999, 'ABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRAABRAKADABRA'),";
const char *cmd = "insert into aaaa (a, b, c, d, e, f, g, h, j, k, l, m, n, p) values ";
char *start;
char *query = (char*)calloc(strlen(cmd) + ((howmany+1) * (strlen(line)+40)) + 1, sizeof(char));
if(!query)
abort();
start = query;
query += sprintf(query, cmd);
for(int i=0; i<howmany; i++)
query += sprintf(query, line, i);
// chop off last comma
query[-1] = 0;
PGresult *res = PQexec(conn, start);
if(PQresultStatus(res) != PGRES_COMMAND_OK)
cout<<"error "<<PQresultErrorMessage(res)<<endl;
PQclear(res);
free(start);
}
static void
pgcopy(void)
{
printf("using copy\n");
char query[1024]={0};
PGresult *res = PQexec(conn, "copy aaaa from STDIN delimiter \',\';");
if(PQresultStatus(res) != PGRES_COPY_IN)
{
fprintf(stderr, "insert command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely();
}
PQclear(res);
for(int i=0; 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);
if(PQputCopyData(conn, query, strlen(query)) != 1)
{
cout<<"error at iteration "<<i<<":"<<PQerrorMessage(conn)<<endl;
break;
}
}
if(PQputCopyEnd(conn, NULL) != 1)
cout<<"error: "<<PQerrorMessage(conn)<<endl;
}
int
main(int argc, char **argv)
{
const char *conninfo = "host=192.168.0.15 dbname=whopper port=5432 user=docsysweb password=docsyswebchaos" ;
PGresult *res;
char type;
if (argc<3)
{
cout<<"usage: <# to insert> <method: PQ(e)xec, PQexec(P)repared, one (I)nsert, (C)OPY>"<<endl;
return -1;
}
sscanf(argv[1], "%d", &howmany);
sscanf(argv[2], "%c", &type);
time_t startt = time(0);
cout<<"starting at "<<asctime(localtime(&startt));
cout<<"inserting "<<howmany<<" records"<<endl;
/* 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();
}
/* 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();
}
PQclear(res);
switch(type)
{
case 'e':
pgexec();
break;
case 'p':
pgprepare();
break;
case 'i':
pginsert();
break;
case 'c':
pgcopy();
break;
default:
fprintf(stderr, "Dont know how to '%c'\n", type);
exit_nicely();
break;
}
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
time_t endt = time(0);
cout<<"Finished at "<<asctime(localtime(&endt))<<"total: "<< endt - startt <<"s"<<endl;
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
//----------------------------------------------------------------------------------------------------------------------------
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
