Sending data to SQLite faster using transaction / commit

[homerun4711]

Hello!

I want to transmit data to a SQLite database but this is done record
be record and takes much to long.

So I decided to use transaction(). But it is still working record by record,
so something must be wrong.

Can you tell me what is wrong?

Kind regards,
HomeR

This is the code:

QSqlDatabase dbSQL = QSqlDatabase::database();
dbSQL.transaction();
populateSQL(RemoteName,Purpose,DateString,value,currQString);
dbSQL.commit();

QSqlDatabase dbSQL = QSqlDatabase::database();
dbSQL.transaction();
populateSQL(RemoteName,Purpose,DateString,value,currQString);
dbSQL.commit();

To copy to clipboard, switch view to plain text mode 

int Banking::populateSQL(QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency)
{
    QSqlQuery query;
 
    query.prepare(
        "INSERT INTO onlinebankingdata ("
        "RemoteName,"
        "Purpose,"
        "Date,"
        "Value,"
        "Currency)"
        "VALUES ("
 
        ":RemoteName,"
        ":Purpose,"
        ":Date,"
        ":Value,"
        ":Currency)");
 
 
    query.bindValue(":RemoteName", RemoteName);
    query.bindValue(":Purpose", Purpose);
    query.bindValue(":Date", Date);
    query.bindValue(":Value", Value );
    query.bindValue(":Currency", Currency);
 
 
 
    bool test = query.exec();
    if (!test)
        {
        QMessageBox::warning(0, QObject::tr("Database Error"),query.lastError().text());
        }
 
    return 0;
}

int Banking::populateSQL(QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency)
{
    QSqlQuery query;

    query.prepare(
        "INSERT INTO onlinebankingdata ("
        "RemoteName,"
        "Purpose,"
        "Date,"
        "Value,"
        "Currency)"
        "VALUES ("

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


    query.bindValue(":RemoteName", RemoteName);
    query.bindValue(":Purpose", Purpose);
    query.bindValue(":Date", Date);
    query.bindValue(":Value", Value );
    query.bindValue(":Currency", Currency);



    bool test = query.exec();
    if (!test)
        {
        QMessageBox::warning(0, QObject::tr("Database Error"),query.lastError().text());
        }

    return 0;
}

To copy to clipboard, switch view to plain text mode 

[wysota]

A transaction is not about batch processing. It's a guarantee that either everything goes to the database or nothing. And in the code here you have just a single record so I'm not sure what exactly you would expect to achieve.

[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