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

Yeah, as noted in the edit up top, i noticed that. And aside from using a hash i'm using exactly the same code as you now.

As for AutoCommit, that is what the Begin/End block is there for, it does the same thing, only locally constrained.
  • Comment on Re^2: Mass inserts with SQLite + CSV + execute_array?

Replies are listed 'Best First'.
Re^3: Mass inserts with SQLite + CSV + execute_array?
by Tanktalus (Canon) on Dec 14, 2008 at 21:48 UTC

    I wasn't aware that DBI used BEGIN and END blocks to temporarily disable AutoCommit. I would suggest actually setting AutoCommit => 0 in your DBI->connect statement if you aren't already.

[reply]
      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]
        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]
      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]

        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]

In Section Seekers of Perl Wisdom