Sending data to SQLite faster using transaction / commit

[homerun4711]

You are right. Since populateSQL is called within a while-loop
I have to put transaction and commit outside of the loop.
Damn monday mornings... Thanks a lot.

[wysota]

It will not make your application faster. If you want it to be faster then either use something like:

bool populateSQL(QSqlQuery query, QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency) {
    query.bindValue(":RemoteName", RemoteName);
    query.bindValue(":Purpose", Purpose);
    query.bindValue(":Date", Date);
    query.bindValue(":Value", Value );
    query.bindValue(":Currency", Currency);
    return query.exec();
}
 
QSqlQuery q;
q.prepare("INSERT INTO onlinebankingdata ("
        "RemoteName,"
        "Purpose,"
        "Date,"
        "Value,"
        "Currency)"
        "VALUES ("
 
        ":RemoteName,"
        ":Purpose,"
        ":Date,"
        ":Value,"
        ":Currency)");
 
for(int i=0;i<...;++i){
  populateSQL(q, ...);
}

bool populateSQL(QSqlQuery query, QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency) {
    query.bindValue(":RemoteName", RemoteName);
    query.bindValue(":Purpose", Purpose);
    query.bindValue(":Date", Date);
    query.bindValue(":Value", Value );
    query.bindValue(":Currency", Currency);
    return query.exec();
}

QSqlQuery q;
q.prepare("INSERT INTO onlinebankingdata ("
        "RemoteName,"
        "Purpose,"
        "Date,"
        "Value,"
        "Currency)"
        "VALUES ("

        ":RemoteName,"
        ":Purpose,"
        ":Date,"
        ":Value,"
        ":Currency)");

for(int i=0;i<...;++i){
  populateSQL(q, ...);
}

To copy to clipboard, switch view to plain text mode 

or use the multi-line insert semantics (passing several VALUES sections in one statement).

[homerun4711]

Hm, putting transaction and commit outside ot the while-loop actually did make my application faster. Beforehand it tool about a minute to insert all records into the SQLite database (with loud harddisk noise) now it is not even noticable, done on the spot.

You suggested a for-loop in your example. I cant use this, because I add an undefined number of records.
I don't get why your example is even faster, could you please explain it with a few words?

[wysota]

You suggested a for-loop in your example. I cant use this, because I add an undefined number of records.

For loop is just a different syntax for the while loop semantics. What loop you use is irrelevant. What is relevant is that you prepare the query once and execute it many times.

I don't get why your example is even faster, could you please explain it with a few words?

It lets the database optimize things better. If running inside a transaction helps then you can merge the two approaches. You can use QSqlQuery::addBindValue() to bind a series of datasets and execute the statement in one go and you can also use QSqlQuery::execBatch() if you bind lists of values in one go.

[ChrisW67]

Sqlite bulk insert performance is substantially improved by performing the inserts within a transaction. Without the explicit transaction each insert becomes a standalone transaction along with all the overhead of file locking and buffer flushing. The encompassing transaction produces just one set of these overheads regardless of the number of inserts. The trade-off is that the bulk inserts either succeed or fail en masse.
http://www.sqlite.org/faq.html#q19