Increase the update speed to PostgreSQL database via libpqxx

Question

I have am trying to use libpqxx library to read and write into a postgreSQL database through C++. The sample database I have is a 3 columns and about 16000 rows.

In my code, I use work.exec to transfer sql query and It took 30+sec to update all 16000 cells in 1 column. I am not sure if I was not doing it correctly,or the write-in time depends on my hard drive speed?

Attached the sample code I used.

#include <string>
#include <vector>
#include <chrono> 
#include <iostream>
#include <fstream>
#include <sstream>

#include <pqxx/pqxx>

using namespace std;
using namespace std::chrono;

auto start = high_resolution_clock::now();

int main(int argc, char* argv[])
{
       //open connection to postgresql database
    pqxx::connection con("host=localhost port=5432 dbname=postgres user=postgres password=P@$$w0rd");
       //"pqxx::work" is an transaction type object in libpqxx, use to transfer SQL query or command
    pqxx::work wrk(con);
       //"pqxx:result" set containing data returned by a query or command
    pqxx::result res = wrk.exec("SELECT * FROM signal");

    for (int i = 0; i < res.size(); i++)
    {
       wrk.exec("UPDATE public.signal SET signalvalue = signalvalue + 1 WHERE indexid ="+to_string(i)+";");
    }

    wrk.commit();

    auto stop = high_resolution_clock::now();
    auto duration = duration_cast<microseconds>(stop - start);
    cout << "Time taken by function: " << duration.count() << " microseconds" << endl;
    cin.get();
    return 0;
}

Answer 1

The performance of your code could be improved by using a prepared statement.

From the libpqxx documentation

If you have an SQL statement that you're going to execute many times in quick succession, it may be more efficient to prepare it once and reuse it. This saves the database backend the effort of parsing complex SQL and figuring out an efficient execution plan. Another nice side effect is that you don't need to worry about escaping parameters.

Your SQL statement would look like

UPDATE public.signal SET signalvalue = signalvalue + 1 WHERE indexid = $1

You can prepare this once, and then insert different values for $1 in your loop.

The libpqxx documentation has an example you can refer to.