How to improve the efficiency of exporting large set of records using QSqlQuery with OCI?

[zhengwei]

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

[SGaist]

Hi and welcome to devnet,

What's queryList ?

Isn't the column count always the same ?

[zhengwei]

@SGaist
thanks!
The 'queryList' declaration as below:
QList<QSqlQuery> queryList;
and the column count is always the same.

[jsulm]

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

[SGaist]

To add to @jsulm, why a QList of QSqlQuery ?

[zhengwei]

@jsulm thanks, i will try and see.

[zhengwei]

@SGaist
I have several different sql queries at the same time for mobile network KPI monitoring purpose, each with its own QSqlQuery.

[SGaist]

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]

[ObiWan]

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

[zhengwei]

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

[SGaist]

Why that series for call to fetchMore ? If you want to fetch everything, then use a while loop

[zhengwei]

@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?

[SGaist]

Without recompiling Qt no, it's a hardcoded value