C++如何将从postgresql查询到的数据分配给一个结构并快速存储在std::vector中

Question

postgresql中有大量数据，超过1000行。postgresql中的数据结构如下：

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)
)

---

C++中的数据结构如下：

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;
};

---

我的意思是，postgresql表中的列名与结构的成员相同。

现在，我想从postgresql表中查询1000，000行数据，并将数据分配给一个具有TICK结构的向量。

我的代码如下：

std::vector<TICK> 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<std::string>()), "yyyyMMdd hh:mm:ss.z");
tick.date = row[2].as<std::string>();
tick.time = row[3].as<std::string>();
tick.instrumentID = row[4].as<std::string>();
tick.preSettlementPrice = row[5].as<double>();
tick.preClosePrice = row[6].as<double>();
tick.preOpenInterest = row[7].as<int>();
tick.openPrice = row[8].as<double>();
tick.lastPrice = row[9].as<double>();
tick.highPrice = row[10].as<double>();
tick.lowPrice = row[11].as<double>();
tick.averagePrice = row[12].as<double>();
tick.upperLimit = row[13].as<double>();
tick.lowerLimit = row[14].as<double>();
tick.bidPrice1 = row[15].as<double>();
tick.bidVolume1 = row[16].as<int>();
tick.askPrice1 = row[17].as<double>();
tick.askVolume1 = row[18].as<int>();
tick.volume = row[19].as<int>();
tick.turnover = row[20].as<double>();
tick.openInterest = row[21].as<int>();
tick.lastVolume = row[22].as<int>();
tick.lastOpeninterest = row[23].as<int>();
tick.ExchangeID = row[24].as<std::string>();

//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;
}

我花了4分钟完成这项工作。从postgresql查询数据需要10秒，但for循环几乎需要4分钟。

我可以发现有2个问题，但我不知道如何解决：

1. 将格式从字符变化更改为QDateTime格式需要很长时间。

   tick.datetime=QDateTime:：fromString(QString:：fromStdString(row[1].asstd:：string(((，"yyyyMMdd hh:mm:ss.z"(；

两列需要像这样更改格式。但我需要准确地比较日期-时间，C++中还有其他时间格式吗？

2. 将每一行数据分配给TICK结构非常耗时。我可以一次将查询结果分配给一个向量吗

有什么解决方案吗？我想快点。非常感谢。

Answer 1

感谢@Sam Varshavchik和@Jesper Juhl的友好帮助。

我启用了优化，提前保留了向量，并创建了一个新的结构，只保留int和double的成员进行测试。

struct TEMPTICK {
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::vector<TEMPTICK> 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();
tickMap.reserve(re.size());
for (auto& row : rs) {
tick.preSettlementPrice = row[5].as<double>();
tick.preClosePrice = row[6].as<double>();
tick.preOpenInterest = row[7].as<int>();
tick.openPrice = row[8].as<double>();
tick.lastPrice = row[9].as<double>();
tick.highPrice = row[10].as<double>();
tick.lowPrice = row[11].as<double>();
tick.averagePrice = row[12].as<double>();
tick.upperLimit = row[13].as<double>();
tick.lowerLimit = row[14].as<double>();
tick.bidPrice1 = row[15].as<double>();
tick.bidVolume1 = row[16].as<int>();
tick.askPrice1 = row[17].as<double>();
tick.askVolume1 = row[18].as<int>();
tick.volume = row[19].as<int>();
tick.turnover = row[20].as<double>();
tick.openInterest = row[21].as<int>();
tick.lastVolume = row[22].as<int>();
tick.lastOpeninterest = row[23].as<int>();

//then I want to push back the tick to a map. Maybe I can do something else like put an tick envent....
tickMap.push_back(tick);
//emit sendTickData(tick);
}
conn.close();
return true;

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

所以现在结构成员只有int和double。循环1000000行需要40秒。是正常速度吗？在我使用pandas和python将1000000行从postgresql加载到数据帧之前，它只花了不到20秒的时间。我认为c++应该比熊猫快得多。