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

stereomatching

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?

mrdebug

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.

stereomatching

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

stereomatching

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

lgeyer

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.

mrdebug

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

stereomatching

Thanks, you give me a hint