Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to improve the efficiency of exporting large set of records using QSqlQuery with OCI?
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved Solved General and Desktop
13 Posts 4 Posters 2.7k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • Z Offline
    Z Offline
    zhengwei
    wrote on last edited by
    #1

    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();
    
    1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Hi and welcome to devnet,

      What's queryList ?

      Isn't the column count always the same ?

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      Z 1 Reply Last reply
      0
      • SGaistS SGaist

        Hi and welcome to devnet,

        What's queryList ?

        Isn't the column count always the same ?

        Z Offline
        Z Offline
        zhengwei
        wrote on last edited by
        #3

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

        1 Reply Last reply
        0
        • jsulmJ Offline
          jsulmJ Offline
          jsulm
          Lifetime Qt Champion
          wrote on last edited by
          #4

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

          https://forum.qt.io/topic/113070/qt-code-of-conduct

          Z 1 Reply Last reply
          0
          • SGaistS Offline
            SGaistS Offline
            SGaist
            Lifetime Qt Champion
            wrote on last edited by
            #5

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

            Interested in AI ? www.idiap.ch
            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

            Z 1 Reply Last reply
            0
            • jsulmJ 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")

              Z Offline
              Z Offline
              zhengwei
              wrote on last edited by
              #6

              @jsulm thanks, i will try and see.

              1 Reply Last reply
              0
              • SGaistS SGaist

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

                Z Offline
                Z Offline
                zhengwei
                wrote on last edited by
                #7

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

                1 Reply Last reply
                0
                • SGaistS Offline
                  SGaistS Offline
                  SGaist
                  Lifetime Qt Champion
                  wrote on last edited by
                  #8

                  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]

                  Interested in AI ? www.idiap.ch
                  Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                  1 Reply Last reply
                  0
                  • O Offline
                    O Offline
                    ObiWan
                    wrote on last edited by
                    #9

                    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;
                    }
                    
                    1 Reply Last reply
                    0
                    • Z Offline
                      Z Offline
                      zhengwei
                      wrote on last edited by
                      #10

                      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();
                      
                      1 Reply Last reply
                      0
                      • SGaistS Offline
                        SGaistS Offline
                        SGaist
                        Lifetime Qt Champion
                        wrote on last edited by
                        #11

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

                        Interested in AI ? www.idiap.ch
                        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                        Z 1 Reply Last reply
                        0
                        • SGaistS SGaist

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

                          Z Offline
                          Z Offline
                          zhengwei
                          wrote on last edited by zhengwei
                          #12

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

                          1 Reply Last reply
                          0
                          • SGaistS Offline
                            SGaistS Offline
                            SGaist
                            Lifetime Qt Champion
                            wrote on last edited by
                            #13

                            Without recompiling Qt no, it's a hardcoded value

                            Interested in AI ? www.idiap.ch
                            Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                            1 Reply Last reply
                            0

                            • Login

                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • Users
                            • Groups
                            • Search
                            • Get Qt Extensions
                            • Unsolved