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



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



  • 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.



  • 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



  • 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



  • 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.



  • 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;
    @



  • Thanks, you give me a hint


Log in to reply
 

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