Re^3: Mass inserts with SQLite + CSV + execute

Replies are listed 'Best First'.
Re^4: Mass inserts with SQLite + CSV + execute_array? by ikegami (Patriarch) on Dec 15, 2008 at 06:46 UTC
AutoCommit is the opposite of being in a transaction. Quote DBI, [`begin_work`] enables transactions (by turning `AutoCommit` off) until the next call to `commit` or `rollback`. After the next `commit` or `rollback`, `AutoCommit` will automatically be turned on again. He should be using `begin_work` and `commit`, though. DBI might not check for `do("BEGIN")`.	[reply] [d/l] [select]
Re^5: Mass inserts with SQLite + CSV + execute_array? by Xenofur (Monk) on Dec 15, 2008 at 11:35 UTC
Didn't help much. Benchmarks: `begin+end 1: the code took:307 wallclock secs (109.22 usr + 35.47 sys = 144.69 CPU) begin+end 2: the code took:279 wallclock secs (107.95 usr + 37.08 sys = 145.03 CPU) begin_work+commit: the code took:297 wallclock secs (107.45 usr + 37.13 sys = 144.58 CPU)` [download]	[reply] [d/l]
Re^4: Mass inserts with SQLite + CSV + execute_array? by Xenofur (Monk) on Dec 15, 2008 at 06:10 UTC
It might be a SQLite oddity, but i assure you it's the same. :) Reference the SQLite documentation on that: http://www.sqlite.org/lang_transaction.html	[reply]
Re^5: Mass inserts with SQLite + CSV + execute_array? by Tanktalus (Canon) on Dec 15, 2008 at 06:28 UTC
Seriously. Try setting AutoCommit => 0 in your DBI->connect statement. As long as we're looking at URLs, check DBI, and search for AutoCommit (there are lots of them). You'll notice a couple of themes: a lot of uses of `AutoCommit => 0` and `default to "on"`. In its default position, DBI will automatically call the commit() function after each statement. By turning off autocommit, you're saying that you're going to take responsibility for calling commit. This would entirely ruin your day for what you think you're doing. The only way this wouldn't be what is happening is if DBD::SQLite intercepted the BEGIN statement and decided to abort auto-commit for that connection. Very unlikely. Please humour me and at least try it before replying again.	[reply] [d/l] [select]
Re^6: Mass inserts with SQLite + CSV + execute_array? by Xenofur (Monk) on Dec 15, 2008 at 11:33 UTC
Ack, mistook you for Ikegami. Gonna try it out in a sec.	[reply]
Re^6: Mass inserts with SQLite + CSV + execute_array? by Xenofur (Monk) on Dec 15, 2008 at 12:06 UTC
Alright, ran it through and thanks to your prodding found another process in the script that i needed to wrap into one transaction. However, disabling autocommit and committing manually versus wrapping in begin/end doesn't make any noticeable difference: `Autocommit off: the code took:176 wallclock secs (106.80 usr + 26.13 sys = 132.92 CPU) Begin+End: the code took:180 wallclock secs (108.42 usr + 25.98 sys = 134.41 CPU)` [download]	[reply] [d/l]
Re^6: Mass inserts with SQLite + CSV + execute_array? by leocharre (Priest) on Dec 16, 2008 at 16:56 UTC
Yea. I double that. This makes a huge difference. $dbh->{AutoCommit} = 0; and then execute your inserts. And then $dbh->commit. This will make something like this anything from ten to 100 times faster.	[reply]