Solved How to improve the efficiency of exporting large set of records using QSqlQuery with OCI?
-
Hi experts,
I have a simple query using OCI: "select * from ctp_common_objects a", the result of which is more than 600k records.
I want to export all the records to a csv file using the following code: However, it's very very very slow for the entire process to be finished.
Is there a more efficient way to export large sets of query results to disk file? Thanks in advance!QFile file(filePath); if(!file.open(QIODevice::WriteOnly)) { sqlError->append(tr("%1:%2 Fail to open file: %3").arg(__FILE__).arg(__LINE__).arg(filePath)); return; } QApplication::setOverrideCursor(Qt::WaitCursor); if(enableDebug) { sqlError->append(tr("exporting %1...").arg(QFileInfo(file).fileName())); qApp->processEvents(); } QTextStream ts(&file); int numCol = queryList[index].record().count(); for(int col = 0; col < numCol; ++col) { ts << queryList[index].record().fieldName(col) << ((col < numCol - 1) ? "," : "\n"); } queryList[index].seek(-1); while(queryList[index].next()) { for(int col = 0; col < numCol; ++col) { ts << queryList[index].value(col).toString() << ((col < numCol - 1) ? "," : "\n"); } } file.close(); QApplication::restoreOverrideCursor();
-
Hi and welcome to devnet,
What's queryList ?
Isn't the column count always the same ?
-
@SGaist
thanks!
The 'queryList' declaration as below:
QList<QSqlQuery> queryList;
and the column count is always the same. -
If the number of columns is always the same then you can do (loop unrolling):
ts << queryList[index].value(0).toString() << ',';
ts << queryList[index].value(1).toString() << ',';
...
ts << queryList[index].value(n).toString();In this case you do not have for loop and you avoid ((col < numCol - 1) ? "," : "\n")
-
To add to @jsulm, why a QList of QSqlQuery ?
-
@jsulm thanks, i will try and see.
-
@SGaist
I have several different sql queries at the same time for mobile network KPI monitoring purpose, each with its own QSqlQuery. -
Then as an additional step to improve performance, use a local copy of the the query, you are doing many useless call to queryList[index]
-
Hi,
I've solved a similar task with QFutureWatcher.
I hope it helps.// H class KExportItem { public: QString tableName; QString exportPath; }; QList<KExportItem> exportItems; // CPP void *exportTableFunction(KExportItem item) { QString msg = "Exporting " + item.tableName + " to " + item.exportPath + item.tableName + ".xml"; qDebug() << msg; // // > > > Export here your data // } void MainWindow::on_btnExport_clicked() { const int MAX_ITEMCOUNT = 50; qDebug() << "on_pushButton3_clicked()"; QFutureWatcher *exportTableFuture = new QFutureWatcher<void>(this); connect(exportTableFuture, SIGNAL(resultReadyAt(int)), SLOT(exportItemFinished(int))); connect(exportTableFuture, SIGNAL(finished()), SLOT(exportFinished())); connect(exportTableFuture, SIGNAL(progressValueChanged(int)), this, SLOT(exportProgressValueChanged(int))); connect(exportTableFuture, SIGNAL(progressTextChanged(const QString &)), this, SLOT(exportProgressTextChanged(const QString &))); for (int n = 0; n < MAX_ITEMCOUNT; n++) { KExportItem item; item.tableName = QString("TABLE_%1").arg(n); item.exportPath = "c:/export/"; exportItems.append(item); } exportTableFuture->setFuture(QtConcurrent::mapped(exportItems, exportTableFunction)); } void MainWindow::exportItemFinished(int itemNumber) { qDebug() << exportItems.at(itemNumber).tableName << " is exported. "; } void MainWindow::exportFinished() { qDebug() << "All tables are exported."; } void MainWindow::exportProgressValueChanged(int progressValue) { qDebug() << "exportProgressValueChanged: " << progressValue; } void MainWindow::exportProgressTextChanged(const QString &progressText) { qDebug() << "exportProgressTextChanged: " << progressText; }
-
Thanks all!
I also found that LAN connection is much better than WLAN:)
Anyway, here comes my modified code, the efficiency of which is neally comparable to the PL/SQL Developer:QApplication::setOverrideCursor(Qt::WaitCursor); sqlError->append(tr("Exporting SQL query to %1... [Start time: %2]").arg(QFileInfo(file).fileName()).arg(QTime::currentTime().toString("hh:mm:ss"))); qApp->processEvents(); QTextStream ts(&file); if(!queryModel) { queryModel = new QSqlQueryModel; } queryModel->setQuery(*query); int numCol = queryModel->record().count(); ts << queryModel->record().fieldName(0); for(int col = 1; col < numCol; ++col) { ts << "," << queryModel->record().fieldName(col); } ts << "\n"; int row = 0; while(true) { QModelIndex index = queryModel->index(row, 0); if(!index.isValid()) { if(queryModel->canFetchMore()) { queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); queryModel->fetchMore(); continue; } else { break; } } ts << queryModel->data(index).toString(); for(int col = 1; col < numCol; ++col) { index = queryModel->index(row, col); ts << "," << queryModel->data(index).toString(); } ts << "\n"; ++row; } /* int numCol = query->record().count(); ts << query->record().fieldName(0); for(int col = 1; col < numCol; ++col) { ts << "," << query->record().fieldName(col); } ts << "\n"; while(query->next()) { ts << query->value(0).toString(); for(int col = 1; col < numCol; ++col) { ts << "," << query->value(col).toString(); } ts << "\n"; // if(queryModel->canFetchMore()) // { // queryModel->fetchMore(); // } } */ sqlError->append(tr("Done![End time: %1]").arg(QTime::currentTime().toString("hh:mm:ss"))); qApp->processEvents(); file.close(); queryModel->clear(); delete queryModel; queryModel = 0; QApplication::restoreOverrideCursor();
-
Why that series for call to fetchMore ? If you want to fetch everything, then use a while loop
-
@SGaist That's good point. I am testing the efficiency using fetchMore, but don't want to wait for 2~3 minitues for trying to fetch all records.
By connect the QSqlQueryModel to QTreeView, I found that each fetchMore will get about 255 records.
BTW, is there any method to modify the internal behavior of fetchMore so that it can fetch more records each time? -
Without recompiling Qt no, it's a hardcoded value