Bulk Insert into sqlite – FMDatabase

Not much to share, just this:

"When you need bulk insert a good number of rows into your sqlite database using FMDatabase, executing a series of INSERT statement is sure to kill performance. Just simply use transaction will drastically optimize performance."

Add the following lines of code

[db beginTransaction];
//DO BULK INSERT HERE
[db commit];

WHY?

When all the INSERTs are put in a transaction, SQLite no longer has to close and reopen the database or invalidate its cache between each statement. It also does not have to do any fsync()s until the very end.

Here's a test: 25000 INSERTs into an indexed table

BEGIN;
CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));
CREATE INDEX i3 ON t3(c);
... 24998 lines omitted
INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');
INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');
COMMIT;

SQLite 2.7.6 - 1.5S