Sending data to SQLite faster using transaction / commit

[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