Modifying many rows in a table

SQLite

SQLite is very slow when doing commits, because of the technique used to ensure data integrity (see SQLite documentation for details).

When a program executes a DML statement like INSERT, it will be automatically committed by SQLite. As result, if you do not enclose the SQL instruction between BEGIN WORK / COMMIT WORK, there will be as many commits as data manipulation statements.

Solution

If a program must modify many rows in a table, execute the SQL statement within a transaction block delimited by BEGIN WORK / COMMIT WORK instructions. This will dramatically speed up the program with SQLite, and even with other non-Informix database servers.

See Performance with transactions.