Simple Postgresql libpq code too slow?
c, c++, libpq, postgresql
Solution
On a TCP connection, each INSERT will cause a TCP round-trip to the database. 50000 inserts done in 5.5 seconds means that one TCP round-trip takes ~0.1ms. You'd have to compare that to TCP benchmarks with your network equipment, but probably you can't expect to go any faster with this method.
You should consider `COPY FROM STDIN` insteads of individual INSERTs. Internally, that will buffer the contents and you're likely to see a considerable speed increase due to much less round-trips to the server.
See http://www.postgresql.org/docs/current/static/libpq-copy.html for the libpq API related to this form of COPY.
Problem
I was working on postgresql using libpq. The code given below is taking a lot of time (timings given at the end of code). ``` #include "stdafx.h" #include <stdlib.h> #include <libpq-fe.h> #include <windows.h> static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int _tmain(int argc, _TCHAR* argv[]) { const TCHAR *conninfo; PGconn *conn; PGresult *res; int nFields, i, j; if (argc > 1) conninfo = argv[1]; else conninfo = _T("hostaddr=192.168.4.171 port=12345 dbname=mydb user=myname password=mypass"); conn = PQconnectdb(conninfo); if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } /* 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); } TCHAR szVal1[200]; TCHAR szVal2[200]; TCHAR szBuffer[200]; TCHAR *paramValues[2]; int paramLengths[2]; int paramFormats[2] = {0,0}; ExecStatusType eStatus; LARGE_INTEGER li; QueryPerformanceFrequency(&li); double dAppFreq = double(li.QuadPart)/1000.0; QueryPerformanceCounter(&li); LONGLONG siStartCounter = li.QuadPart; TCHAR szStmt[512] = {0}; _tcscpy_s(szStmt, 512, _T("Insert50k")); Oid oidTypes[2] = {0,0}; PGresult *pRes = PQprepare(conn, szStmt, _T("insert into details values($1,$2);"), 2, oidTypes); QueryPerformanceCounter(&li); LONGLONG siEndCounter = li.QuadPart; LONGLONG siLoop = 0; double dDiff = (siEndCounter - siStartCounter)/dAppFreq; printf("Prepared %.2lf\n", dDiff); for(int i=0; i<50000; i++) { _stprintf_s(szVal1, 200, _T("%d"), i); _stprintf_s(szVal2, 200, _T("Detail%d"), i); paramValues[0] = szVal1; paramValues[1] = szVal2; paramLengths[0] = _tcslen(szVal1); paramLengths[1] = _tcslen(szVal2); siStartCounter = siEndCounter; pRes = PQexecPrepared(conn, szStmt, 2, paramValues, paramLengths, paramFormats, 0); QueryPerformanceCounter(&li); siEndCounter = li.QuadPart; siLoop += (siEndCounter - siStartCounter); eStatus = PQresultStatus(res); if (!res || (eStatus != PGRES_COMMAND_OK) ) { PQclear(res); exit_nicely(conn); } } dDiff = siLoop/dAppFreq; printf("Inserted %.2lf\n", dDiff); siStartCounter = siEndCounter; _tcscpy_s(szBuffer,200, _T("select count(*) from programdetails;")); res = PQexec(conn, szBuffer); eStatus = PQresultStatus(res); if (!res || (eStatus != PGRES_TUPLES_OK) ) { PQclear(res); exit_nicely(conn); } /* first, print out the attribute names */ nFields = PQnfields(res); for (i = 0; i < nFields; i++) printf("%-15s", PQfname(res, i)); printf("\n\n"); /* next, print out the rows */ for (i = 0; i < PQntuples(res); i++) { for (j = 0; j < nFields; j++) printf("%-15s", PQgetvalue(res, i, j)); printf("\n"); } QueryPerformanceCounter(&li); siEndCounter = li.QuadPart; dDiff = (siEndCounter - siStartCounter)/dAppFreq; printf("Printed %.2lf\n", dDiff); /* end the transaction */ res = PQexec(conn, "COMMIT"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ``` A sample output (in msecs): ``` Prepared 0.55 Inserted 5527.52 count 50000 Printed 7.58 ``` The query here is prepared first, and then executed. This simple insertion takes about 5.5 seconds. Is there a better way to do the same or am I doing something wrong here?