This is a tip on how to speed up bulk inserts in
SQLite. In case, you don't know
SQLite is an SQL database implemented as a small, yet very fast library,
which, accesses everything from the file-system. Furthermore, it is open-source,
public domain, and reliable.
Recently, I wanted to create a server-side script to individually display my
fortune cookie quotes
- each on its own page based on its ID. In order to speed up the lookup, I
decided to store everything in a
database, be it
the raw file-system, something like Berkeley DB or Tokyo Cabinet, or an
SQL-based database. I ended up using SQLite, which worked pretty well.
I've written a program to populate the database by parsing the data from
the XML source files and extracted the data from there. I prepared an INSERT
my $insert_sth = $dbh->prepare(<<'EOF');
INSERT INTO fortune_cookies (collection_id, str_id, title, text)
VALUES(?, ?, ?, ?)
(It started a bit simpler, but that's what I have now.)
And then I executed it for each record. It took a lot of time to complete -
many minutes. So I consulted #perl
about it and got a few suggestions:
That I should disable the so-called “shared database” mode, which is the
default SQLite mode that assumes other processes on the machine might
access the database.
That I should turn off Autocommit and commit in batches with more than
one record in each in every transaction.
The number two option seemed the most likely, and I tried commiting in batches
of 50 records each and it made the populating program finish in a few seconds.
So I was very happy.
I guess I can try the shared database mode later on, since I populate the
database offline. Now the program for displaying every quote individually is
working nicely, and I am very happy. Thanks to the #perl people for helping