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

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.

Comment on Re^5: Mass inserts with SQLite + CSV + execute_array? Select or Download Code

Replies are listed 'Best First'.
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]