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