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. [Solved]Insert and update a lot of data into sqlite(time needed)
Forum Updated to NodeBB v4.3 + New Features

[Solved]Insert and update a lot of data into sqlite(time needed)

Scheduled Pinned Locked Moved General and Desktop
7 Posts 3 Posters 9.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.
  • S Offline
    S Offline
    stereomatching
    wrote on 9 Oct 2012, 05:50 last edited by
    #1

    first solution
    @
    QVariantList value_first;
    QVariantList value_second;
    std::for_each(std::begin(data), std::end(data), [&](value_type const &value)
    {
    value_first << value.first;
    value_second << value.second;
    });

    QSqlQuery qry(QSqlDatabase::database(db_->connectionName()));
    qry.prepare("UPDATE table_data SET number = ? WHERE address = ?");
    qry.addBindValue(value_first);
    qry.addBindValue(value_second);   
    if (!qry.execBatch())
    {       
        qDebug() << qry.lastError();        
    }
    

    @

    second solution
    @

    QSqlQuery qry(QSqlDatabase::database(db_->connectionName()));
    qry.prepare("UPDATE table_data SET number = ? WHERE address = ?");
    std::for_each(std::begin(data), std::end(data), [&](value_type const &value)
    {       
       qry.addBindValue(value_first);
       qry.addBindValue(value_second);      
       qry.exec&#40;&#41;;
    }&#41;;
    

    @

    I want to speed up the time of update, so I want the program
    access the db one time only.How could I do that?

    1 Reply Last reply
    0
    • M Offline
      M Offline
      mrdebug
      wrote on 9 Oct 2012, 06:01 last edited by
      #2

      What is your problem exactly? The time needed? In this case you must remember to use "begin exclusive transaction" before the insert or update and at the end to use "commit". In this mode the time needed to complete is much less.

      Need programmers to hire?
      www.labcsp.com
      www.denisgottardello.it
      GMT+1
      Skype: mrdebug

      1 Reply Last reply
      0
      • S Offline
        S Offline
        stereomatching
        wrote on 9 Oct 2012, 06:24 last edited by
        #3

        bq. The time needed?

        yes, time needed.Could you show me how to do it?I do some test and fail to update
        the data properly by QSqlquery

        Thanks for your advices

        1 Reply Last reply
        0
        • S Offline
          S Offline
          stereomatching
          wrote on 9 Oct 2012, 07:13 last edited by
          #4

          After some study + trial and error
          @

          std::for_each(std::begin(data), std::end(data), [&](value_type const &value)
          {       
             value_first << value.first;
             value_second << value.second;
             value_date << date;
          });
           qDebug() << "add_table_data : " << db_->transaction(); //start transaction
          qry.addBindValue(value_first);
          qry.addBindValue(value_second);
          qry.addBindValue(value_date);        
          qDebug() << "add_table_data : " << db_->commit();    //commit transaction
          if (!qry.execBatch())
          {
              #ifdef DEBUG_OK
              qDebug() << qry.lastError();
              #endif
          }
          

          @

          Don't know this is the solution you mention about or not
          Still need several seconds to handle about 3000 data

          ps : 1500 insert, 1500 update

          1 Reply Last reply
          0
          • L Offline
            L Offline
            lgeyer
            wrote on 9 Oct 2012, 08:20 last edited by
            #5

            There is a set of "pragmas":http://www.sqlite.org/pragma.html closely related to performance, above all journal_mode = OFF, synchronous = OFF, locking_mode = EXCLUSIVE, foreign_keys = OFF and temp_store = MEMORY.

            You'll gain a huge performance boost, but make sure you've read what those pragmas actually mean, understood their consequences and you will set them to resonable values as soon as you have left the performace critical section.

            1 Reply Last reply
            0
            • M Offline
              M Offline
              mrdebug
              wrote on 9 Oct 2012, 08:33 last edited by
              #6

              I use this approach:
              @ QSqlQuery query(db);
              if (!query.exec("begin exclusive transaction;")) {
              Error= query.lastError().text();
              return false;
              } else {
              query.prepare("update Cameras set Rotation= :Rotation, FpsWatch= :FpsWatch where id_camera= :id_camera");
              query.bindValue(":Rotation", QVCameras[count].Rotation);
              query.bindValue(":FpsWatch", QVCameras[count].FpsWatch);
              query.bindValue(":id_camera", QVCameras[count].id_camera);
              if (!query.exec()) {
              Error= query.lastError().text();
              return false;
              } else {
              if (!query.exec("commit;")) {
              Error= query.lastError().text();
              return false;
              } else return true;
              @

              Need programmers to hire?
              www.labcsp.com
              www.denisgottardello.it
              GMT+1
              Skype: mrdebug

              1 Reply Last reply
              0
              • S Offline
                S Offline
                stereomatching
                wrote on 14 Oct 2012, 05:44 last edited by
                #7

                Thanks, you give me a hint

                1 Reply Last reply
                0

                1/7

                9 Oct 2012, 05:50

                • Login

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