Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Monday November 24, 2003
02:00 AM

DBD::SQLite performance

[ #15972 ]

In my caffeine-induced delerium, I seemed to remember some notes from Matt Sergeant in the early days of DBD::SQLite to the effect that he did something different and sped things way up. I scanned his entire journal and found out that what he did was turn off auto-commit and just commit every thousands INSERTS or so. I'm making a note of this here so I can remember it and also in case it will help anyone else.

Apparently SQLite effectively does a commit on every statement when you have auto-commit on.

For the record, Monster energy drink compares horribly to Mountain Dew Amp, although it's a mere 16 cents more for twice as much. That said, both leave me with the unmistakeable feeling of having been ripped off.

Update: don't try to commit every 18,000 records, even if you're tempted by the fact that each of your data files contains about 18,000 records and you think you'll gain the security of being able to rollback when you interrupt in the middle of your data load. Committing every 50, 100, 500, or 1000 statements is good, depending on your circumstances.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Apparently SQLite effectively does a commit on every statement when you have auto-commit on.

    Is there any other way it could behave with auto-commit on?
    • Yes. Autocommit describes the behavior the driver takes when an exit occurs without an explicit commit or rollback. Upon exit, the driver will commit if autocommit is true and rollback if autocommit is false. There's nothing in there that says it has to commit every SQL statement and indeed, if you'll try a simple experiment with DBD::Oracle, you'll see that with autocommit on it doesn't commit until you tell it to or exit.

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
      • You describe the behaviour for a "Databases in which a transaction is always active". Grep the DBI docs for that line for more details.

        SQLite is more of the category: "Databases in which a transaction must be explicitly started"

        It could have behaved more like Oracle, but I didn't know that was an option.
  • IIRC, if you start a transaction, then SQL-Lite will commit once per transaction, with auto commit. Think I remember reading something like that ? So chunk your inserts round a transaction and it will only commit once? Most databases do it that way, I think.

    For some purposes, it may be ok to just checkpoint (to a log file, for instance) once every n rows, instead of committing.If something breaks, you can inspect the log and reload too.