Insert data into sqlite is very slow by QSqlQuery

[tham]

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

[yuvaram]

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.

[SGaist]

Hi,

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

[JonB]

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

[tham]

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

[zhiyiYo]

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