On 28 Feb 2014, at 5:18pm, L. Wood <lwoo...@live.com> wrote: > If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and > _step() for each "DELETE" query to be very fast, but the _step() for the "END > TRANSACTION" query to take most (99%) of the time? > > Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" > in a similar way slow as molasses, and wrapping many inserts in a transaction > recommended?
The way SQL works is that theoretically you cannot do any command outside a transaction. No changes are actually made to the database except when a transaction ends. Therefore if you issue "BEGIN TRANSACTION" all the work is actually done when you execute "END TRANSACTION". Until then, SQL is just making a list of the changes you may want to do sometime in the future. Therefore the "END TRANSACTION" is the command that takes all the time. However, SQLite tries to be useful to you. If you forget to do "BEGIN TRANSACTION" before you execute an INSERT or a DELETE, instead of returning an error message it helpfully wraps that single command inside a transaction for you. Therefore that single command makes changes to the database. So it takes a long time. This is why issuing many INSERT or DELETE commands with no transaction declared takes so long. For each separate command SQLite has to do make the change you asked for then do lots of work to make sure that the database file is synchronised with the journal file and the disk is updated to reflect both changes. If you wrap lots of commands inside one transaction SQLite only has to do this synchronization once. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
