Insert data into sqlite is very slow by QSqlQuery



  • 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



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

  • Lifetime Qt Champion

    Hi,

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



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



  • 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


Log in to reply
 

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