Using transactions to elevate the performance of SQLite insert

Yesterday found that SQLite insert performance is very low, search for a bit, in fact, the insertion of SQLite can be done 50,000 per second, but the speed of processing transactions slow:

INSERT is really slow-i can only do few dozen inserts per second

Actually, SQLite would easily do 50,000 or more inserts statements per second on an average desktop computer. But it would only do a few dozen transactions per second. Transaction speed was limited by the rotational speed of your disk drive. A transaction normally requires both complete rotations of the disk platter, which on a 7200RPM disk drive limits your to AB Out transactions per second.

Transaction speed was limited by disk drive speed because (by default) SQLite actually waits until the data really is Safel Y stored on the disk surface before the transaction are complete. That's the, if you suddenly lose power or if your OS crashes, the your data is still safe. For details, read about atomic commit in SQLite.

By default, each of the INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN ... COMMIT then all the inserts is grouped into a single transaction. The time needed to commit the transaction are amortized over all the enclosed INSERT statements and so the time per insert statement is greatly reduced.

SQLite faq#19

My original code does not use transactions, so each INSERT statement defaults to a transaction. The solution is to add transactions, and the time to execute SQL is reduced from 10 seconds to 0.07 seconds.

After discovering this, I tried to add the possibility to the business, but the original program has a logic, is to execute a lot of inserts, if the primary key conflict is naturally ignored. But if you turn this heap of SQL into a transaction, it will affect the insertion of the correct data, so you can change the INSERT statement to insert or ignore:

Insert or ignore into test (ID, key) VALUES (20001, ' kyfxbl ');

And then put it in a transaction, the efficiency is greatly improved.