Qt sqlite Bulk Insert Optimization (SQLite defaults to each statement as a separate transaction) good

When you use SQLite to store data, you find that the insertion speed is too slow and the program has been running for almost five minutes before inserting less than 3,000. Online search data only found thatsqlite this file database and the MySQL mechanism is different, each transaction has to open and close the file steps, SQLite by default, each statement as a separate transaction. When I insert data, there will be a lot of file IO operation, the efficiency is not high. the insertion efficiency can be significantly improved by putting multiple inserts into a single transaction.

The methods for using transactions in QT are as follows:

Qsqldatabase app_database;
Setting Database Parameters
// ...
App_database.transaction (); Start a transaction
Execute SQL operation
// ...
App_database.commit (); Submit

However, you will need to obtain an auto-generated ID after insertion in your project. It can be obtained with the Qsqlquery::lastinsertid () method, but after a bulk insert using a transaction, Lastinsertid () cannot be used because we need to get the ID set of the inserted data. To assign an ID to an object, you also need to make sure that the one by one correspondence to the object.

Because the parent object in the project contains more than one child object and is saved in the Qvector container, I use the order of the Qvector to guarantee the object's correspondence. Saves the sub-object in the Qvector index in the database, after bulk inserting the child object, the ID of all child objects and index are removed, according to index set the corresponding object ID. This allows you to bulk insert child objects in a transaction and then populate the child object's ID.

http://windrocblog.sinaapp.com/?p=835

Qt sqlite Bulk Insert Optimization (SQLite defaults to each statement as a separate transaction) good