in reply to Re^3: Mass inserts with SQLite + CSV + execute_array?
in thread Mass inserts with SQLite + CSV + execute_array?

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
  • Comment on Re^4: Mass inserts with SQLite + CSV + execute_array?

Replies are listed 'Best First'.
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]
      Ack, mistook you for Ikegami. Gonna try it out in a sec.
[reply]
      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]

      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]

In Section Seekers of Perl Wisdom