C++ how to assign data queryed from postgresql to a struct and store in std::vector fast

Question

I have large data in postgresql, more than 1, 000, 000 rows.The data structure in postgresql is like this:

CREATE TABLE tickdata
(
    datetime character varying COLLATE pg_catalog."default" NOT NULL,
    date character varying COLLATE pg_catalog."default" NOT NULL,
    "time" character varying COLLATE pg_catalog."default" NOT NULL,
    instrumentid character varying COLLATE pg_catalog."default" NOT NULL,
    presettlementprice double precision NOT NULL,
    precloseprice double precision NOT NULL,
    preopeninterest bigint NOT NULL,
    openprice double precision NOT NULL,
    lastprice double precision NOT NULL,
    highprice double precision NOT NULL,
    lowprice double precision NOT NULL,
    averageprice double precision NOT NULL,
    upperlimit double precision NOT NULL,
    lowerlimit double precision NOT NULL,
    bidprice1 double precision NOT NULL,
    bidvolume1 bigint NOT NULL,
    askprice1 double precision NOT NULL,
    askvolume1 bigint NOT NULL,
    volume bigint NOT NULL,
    turnover double precision NOT NULL,
    openinterest bigint NOT NULL,
    lastvolume bigint NOT NULL,
    lastopeninterest bigint NOT NULL,
    gatewayname character varying COLLATE pg_catalog."default" NOT NULL,
    exchangeid character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT hc1605_pkey PRIMARY KEY(datetime)
)

And the data struct in C++ is like this :

struct TICK {

    std::string datetime;
    std::string date;
    std::string time;
    std::string instrumentID;
    double preSettlementPrice;
    double preClosePrice;
    int preOpenInterest;
    double openPrice;
    double lastPrice;
    double highPrice;
    double lowPrice;
    double averagePrice;
    double upperLimit;
    double lowerLimit;
    double bidPrice1;
    int bidVolume1;
    double askPrice1;
    int askVolume1;
    int volume;
    double turnover;
    int openInterest;
    int lastVolume;
    int lastOpeninterest;
    std::string ExchangeID;

};

I mean, the column names in the table of postgresql is same as the members of the structure.

Now I want to query 1, 000, 000 row of data from postgresql table and assign the data to a vector with TICK structure.

My code is as following :

std::vector tickMap;
std::string sqlQuery = "SELECT * FROM tickdata where datetime between 'XXXXX' and 'XXXXX'";
pqxx::connection conn(testTickConn);
try
{
    start = clock();
    pqxx::nontransaction dbQuery(conn);
    pqxx::result rs = (dbQuery.exec(sqlQuery));
    dbQuery.commit();
    finish = clock();
    double duration = (double)(finish - start) / CLOCKS_PER_SEC;
    emit sendLogData(QString::fromStdString("datamanager load " + instrumentID + " " + std::to_string(rs.size()) + "takes " + std::to_string(duration) + " seconds"));
    start = clock();
    for (auto& row : rs) {

        tick.datetime = QDateTime::fromString(QString::fromStdString(row[1].as()), "yyyyMMdd hh:mm:ss.z");
        tick.date = row[2].as();
        tick.time = row[3].as();
        tick.instrumentID = row[4].as();
        tick.preSettlementPrice = row[5].as();
        tick.preClosePrice = row[6].as();

        tick.preOpenInterest = row[7].as();
        tick.openPrice = row[8].as();
        tick.lastPrice = row[9].as();
        tick.highPrice = row[10].as();
        tick.lowPrice = row[11].as();
        tick.averagePrice = row[12].as();
        tick.upperLimit = row[13].as();
        tick.lowerLimit = row[14].as();
        tick.bidPrice1 = row[15].as();
        tick.bidVolume1 = row[16].as();
        tick.askPrice1 = row[17].as();
        tick.askVolume1 = row[18].as();
        tick.volume = row[19].as();
        tick.turnover = row[20].as();
        tick.openInterest = row[21].as();
        tick.lastVolume = row[22].as();
        tick.lastOpeninterest = row[23].as();
        tick.ExchangeID = row[24].as();


        //then I want to push back the tick to a map. Maybe I can do something else like put an tick envent....
        tickMap['tickdata'].push_back(tick);
        //emit sendTickData(tick);
    }

    conn.close();

    return true;



}
catch (const std::exception& ex)
{
    emit sendLogData(QString::fromStdString(ex.what()));
    return false;

}

It takes me 4 minutes to finish this job. query data from postgresql takes 10 seconds, but the for loop takes me almost 4 minutes.

I can find out there are 2 problems but I don't know how to fix it:

It take long time to change the format from character varying to QDateTime format.

tick.datetime = QDateTime::fromString(QString::fromStdString(row[1].asstd::string()), "yyyyMMdd hh:mm:ss.z");

two columns need to change format like this. But I need to compare the date time exactly, any other format for time in C++?

assign each row of data to a TICK struct is very time consuming. Can I assign the query result to a vector in one time?

Any solutions? I want to make it fast. Thank you.

C++ how to assign data queryed from postgresql to a struct and store in std::vector fast

Answers (1)

Related Questions