On 10 Oct 2016, at 6:32am, Hayden Livingston <halivings...@gmail.com> wrote:
> The problem is if I don't do a BEGIN TRANSACTION and do my inserts, it > takes absolutely forever for my program to finish (we're talking > hours). In traditional SQL it would be an error to try to do INSERT without first doing BEGIN. You would get an error message because you were trying to make changes outside a transaction. SQLite is helpful. If you forget to do BEGIN then it wraps your command in BEGIN ... command ... END for you. > If instead I do it in a single transaction (i.e. BEGIN TRANSACTION, 1 > million inserts, END) it dramatically improves the time. This is normal. Some changes to the database file must be made for each INSERT/UPDATE/DELETE. But other changes to the database file need to be made just once per transaction. Reducing the number of transactions means that fewer changes need to be made, so it's faster. (This explanation is simplified for brevity.) > Is there a way to get this behavior without transactions? The reason > is most of the times the program is terminated by a user action and I > don't get a chance to END the transaction. There's always a transaction. Either the individual ones created automatically or the ones you create yourself using BEGIN. If your computer/software crashes during a transaction the entire transaction gets lost when the file is reopened. > I'm considering doing "periodic transactions", i.e. buffer in my > application X statements and club them together. Yes, this is a common way to compromise between speed and integrity. X is usually 1000 or 10000 but can be different depending on your pattern of use. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
