-
Bug
-
Resolution: Incomplete
-
P3: Somewhat important
-
None
-
5.10.1
-
None
Hello,
I am experiencing extremely slow insert using Postgres. My app can only process about 1000 inserts every 17ish seconds.
It seems that the QPSQL driver does not support batch executions even though the underlying libpq driver does.
This thread on StackOverflow makes two suggestions:
https://stackoverflow.com/questions/7782842/fast-batch-executions-in-postgresql
- Prepare Query > Start Transaction > Exec in Loop > Commit
- Work around the driver limitation by using Postgres's unnest function.
- Doesn't work as you cannot call bindValue with a QVariantList and have it automatically convert to a Postgres array literal.
My apps insertion code:
(Performance is EXACTLY identical to using query.batchExec())
void APPSQLWriter::crxtWriteQuotes(const QVarLengthArray<AppQuote> "es)
{
this->setIsBusy(true);
this->m_writeElapsedTimer.restart();
qInfo() << this << "Write Start: Length" << quotes.length();
QSqlDatabase dbConnection = QSqlDatabase::database(this->m_connectionName);
if (dbConnection.isOpen() == false || dbConnection.isValid() == false)
{
qWarning() << "APPSQLWriter: Write failed on bad connection";
Q_ASSERT(false);
emit this->error();
return;
}
// Start a transaction
Q_ASSERT(dbConnection.transaction() == true);
QSqlQuery query(dbConnection);
static const QString statement = QStringLiteral("SELECT app.append_quote(?, ?, ?, ?, ?, ?, ?, ?, ?);");
bool prepared = query.prepare(statement);
Q_ASSERT(prepared == true);
for (const AppQuote &cQuote : quotes)
{
query.bindValue(0, cQuote.symbol());
query.bindValue(1, cQuote.condition());
query.bindValue(2, cQuote.bidExchangeId());
query.bindValue(3, cQuote.askExchangeId());
query.bindValue(4, cQuote.bidPrice());
query.bindValue(5, cQuote.askPrice());
query.bindValue(6, cQuote.bidSize());
query.bindValue(7, cQuote.askSize());
query.bindValue(8, cQuote.timeStampMs());
if (query.exec() == false)
{
qDebug() << "Failed to exec";
Q_ASSERT(false);
}
}
// End Transaction
Q_ASSERT(dbConnection.commit() == true);
this->m_writeDurationMs = this->m_writeElapsedTimer.restart();
qDebug() << this << "Finished" << this->m_writeDurationMs;
this->setIsBusy(false);
}
Postgres and libpq both support Batch operations along with another form of mass insertion called the COPY command:
DOCS1: https://www.postgresql.org/docs/current/static/sql-copy.html
DOCS2: https://www.postgresql.org/docs/current/static/libpq-copy.html
Example: https://www.safaribooksonline.com/library/view/practical-postgresql/9781449309770/ch04s03.html
Batch operations for Postgres should be available since the underlying driver supports them.
Implementation of the COPY command, though very database specific, would be even better.