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. Insert data into sqlite is very slow by QSqlQuery
Forum Updated to NodeBB v4.3 + New Features

Insert data into sqlite is very slow by QSqlQuery

Scheduled Pinned Locked Moved Solved General and Desktop
6 Posts 5 Posters 5.1k Views 2 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.
  • T Offline
    T Offline
    tham
    wrote on 15 Jan 2018, 16:57 last edited by tham
    #1

    OS : ubuntu16.04.1
    Qt : Qt5.9.2

    Codes to create db

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(db_name);
        if(!db.open()){
            throw std::runtime_error("Cannot open db " + db_name.toStdString());
        }
       //this work on my pc
        db.exec("PRAGMA synchronous = OFF");
        if(db.lastError().isValid()){
            qDebug()<<"synchronous=OFF : "<<db.lastError();
        }
        //this work on my pc too
        db.exec("PRAGMA journal_mode = MEMORY");
        if(db.lastError().isValid()){
            qDebug()<<"synchronous=OFF : "<<db.lastError();
        }
    

    Insert codes

        QSqlQuery query;
        query.exec(QString("CREATE TABLE annotations("
                           "id INT, "
                           "room INT, "
                           "name TEXT,"
                           "UNIQUE(id,"
                           "room,"
                           "name) ON CONFLICT REPLACE"
                           ")"));
        if(query.lastError().isValid()){
            qDebug()<<__func__<<":"<<query.lastError();
        }
        query.prepare("INSERT INTO annotations (id, room, name) "
                      "VALUES (?,?,?)");
    
        //qDebug()<<"can start a transaction:"<<QSqlDatabase::database().transaction();
        data_collector const &data_table = create_data();            
        query.addBindValue(data_table.id);
        query.addBindValue(data_table.room);
        query.addBindValue(data_table.name);
        query.execBatch();
        if(query.lastError().isValid()){
            qDebug()<<__func__<<":"<<query.lastError();
        }
        //qDebug()<<"end transaction:"<<QSqlDatabase::database().commit();
    

    Either with or without open transaction, insertion speed is very slow, it took more than 200 seconds to insert 37000 data. Compile with release version. What kind of error I did?

    Thanks

    J 1 Reply Last reply 16 Jan 2018, 19:48
    0
    • Y Offline
      Y Offline
      yuvaram
      wrote on 15 Jan 2018, 17:35 last edited by
      #2

      Hi @tham
      While working with database few steps need to be taken.

      1. Create database class as singleton instance.
        Because for every insertion opening database again and again continuously is not
        recommended.
      2. Not recommended to DB operations in UI thread, use separate thread.

      Yuvaram Aligeti
      Embedded Qt Developer
      : )

      1 Reply Last reply
      1
      • S Offline
        S Offline
        SGaist
        Lifetime Qt Champion
        wrote on 15 Jan 2018, 21:22 last edited by
        #3

        Hi,

        @yuvaram there's no need for any database singleton class. QSqlDatabase already provide everything needed in that regard.

        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
        1
        • T tham
          15 Jan 2018, 16:57

          OS : ubuntu16.04.1
          Qt : Qt5.9.2

          Codes to create db

          QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
              db.setDatabaseName(db_name);
              if(!db.open()){
                  throw std::runtime_error("Cannot open db " + db_name.toStdString());
              }
             //this work on my pc
              db.exec("PRAGMA synchronous = OFF");
              if(db.lastError().isValid()){
                  qDebug()<<"synchronous=OFF : "<<db.lastError();
              }
              //this work on my pc too
              db.exec("PRAGMA journal_mode = MEMORY");
              if(db.lastError().isValid()){
                  qDebug()<<"synchronous=OFF : "<<db.lastError();
              }
          

          Insert codes

              QSqlQuery query;
              query.exec(QString("CREATE TABLE annotations("
                                 "id INT, "
                                 "room INT, "
                                 "name TEXT,"
                                 "UNIQUE(id,"
                                 "room,"
                                 "name) ON CONFLICT REPLACE"
                                 ")"));
              if(query.lastError().isValid()){
                  qDebug()<<__func__<<":"<<query.lastError();
              }
              query.prepare("INSERT INTO annotations (id, room, name) "
                            "VALUES (?,?,?)");
          
              //qDebug()<<"can start a transaction:"<<QSqlDatabase::database().transaction();
              data_collector const &data_table = create_data();            
              query.addBindValue(data_table.id);
              query.addBindValue(data_table.room);
              query.addBindValue(data_table.name);
              query.execBatch();
              if(query.lastError().isValid()){
                  qDebug()<<__func__<<":"<<query.lastError();
              }
              //qDebug()<<"end transaction:"<<QSqlDatabase::database().commit();
          

          Either with or without open transaction, insertion speed is very slow, it took more than 200 seconds to insert 37000 data. Compile with release version. What kind of error I did?

          Thanks

          J Offline
          J Offline
          JonB
          wrote on 16 Jan 2018, 19:48 last edited by JonB
          #4

          @tham
          https://forum.qt.io/topic/20465/solved-insert-and-update-a-lot-of-data-into-sqlite-time-needed gives some clues. I think you need to try a couple of the suggestions for pragmas and/or exclusive access/locking. Also read http://www.sqlite.org/faq.html#q19.

          1 Reply Last reply
          1
          • T Offline
            T Offline
            tham
            wrote on 18 Jan 2018, 04:13 last edited by
            #5

            I find the solution, the answer is do not use execBatch, this api is ultra slow for sqlite database, you should start a transaction first and bind your value in for loop + exec

            qDebug()<<"can start a transaction:"<<QSqlDatabase::database().transaction();
            for(int i = 0; i != data_table.id.size(); ++i){
              query.bindValue(0, data_table.id[i]);
              query.bindValue(1, data_table.room[i]);
              query.bindValue(2, data_table.name[i]);
              query.exec();
            }
            qDebug()<<"end transaction:"<<QSqlDatabase::database().commit();
            

            With this solution, I am able to insert 663581 data in one second, much faster than execBatch(around 180 data in one second). Both of them have the same pragma setting.

            PRAGMA synchronous = OFF
            PRAGMA journal_mode = MEMORY

            zhiyiYoZ 1 Reply Last reply 25 Jan 2022, 00:41
            2
            • T tham
              18 Jan 2018, 04:13

              I find the solution, the answer is do not use execBatch, this api is ultra slow for sqlite database, you should start a transaction first and bind your value in for loop + exec

              qDebug()<<"can start a transaction:"<<QSqlDatabase::database().transaction();
              for(int i = 0; i != data_table.id.size(); ++i){
                query.bindValue(0, data_table.id[i]);
                query.bindValue(1, data_table.room[i]);
                query.bindValue(2, data_table.name[i]);
                query.exec();
              }
              qDebug()<<"end transaction:"<<QSqlDatabase::database().commit();
              

              With this solution, I am able to insert 663581 data in one second, much faster than execBatch(around 180 data in one second). Both of them have the same pragma setting.

              PRAGMA synchronous = OFF
              PRAGMA journal_mode = MEMORY

              zhiyiYoZ Offline
              zhiyiYoZ Offline
              zhiyiYo
              wrote on 25 Jan 2022, 00:41 last edited by
              #6

              @tham said in Insert data into sqlite is very slow by QSqlQuery:

              I find the solution, the answer is do not use execBatch, this api is ultra slow for sqlite database, you should start a transaction first and bind your value in for loop + exec

              qDebug()<<"can start a transaction:"<<QSqlDatabase::database().transaction();
              for(int i = 0; i != data_table.id.size(); ++i){
                query.bindValue(0, data_table.id[i]);
                query.bindValue(1, data_table.room[i]);
                query.bindValue(2, data_table.name[i]);
                query.exec();
              }
              qDebug()<<"end transaction:"<<QSqlDatabase::database().commit();
              

              With this solution, I am able to insert 663581 data in one second, much faster than execBatch(around 180 data in one second). Both of them have the same pragma setting.

              PRAGMA synchronous = OFF
              PRAGMA journal_mode = MEMORY

              AwesomeπŸ˜†

              γ†γγ˜γ‚ƒγͺγ„γ‚ˆ

              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