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

  • Lifetime Qt Champion

    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.


  • Moderators

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


  • Lifetime Qt Champion

    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.


  • Lifetime Qt Champion

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

  • Lifetime Qt Champion

    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?


  • Lifetime Qt Champion

    Without recompiling Qt no, it's a hardcoded value


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.