Loading bulk data into SQLite

[hangon]

In attempting to load a log file into SQLite, it took 20 minutes of disk thrashing. Super search mostly pointed to the fact that databases typically include a loading utility, but it appears that SQLite doesn't have one. RTFM didn't help much, and I was about to post the speed question in SOPW when I found the answer on SQLite's benchmark page. The trick is to do it all in one transaction. The speed improvement was over 200x for loading 50k rows in my test case. Here it is in case someone else needs to know:

# first attempt
...
my $start = time;
my $dbh = DBI->connect("DBI:SQLite:$dbfile") or die;

my $sth = $dbh->prepare( qq(INSERT INTO 'logentries' 
    ('col1', 'col2', 'col3', 'col4') values (?,?,?,?) ));

$sth->execute($_->[0], $_->[1], $_->[2], $_->[3]) for @rows;

print "et: ", time - $start, " sec\n";  
# et: 1082 seconds

# single transaction
...
my $start = time;
my $dbh = DBI->connect("DBI:SQLite:$dbfile") or die;

$dbh->do('BEGIN');

my $sth = $dbh->prepare( qq(INSERT INTO 'logentries' 
    ('col1', 'col2', 'col3', 'col4') values (?,?,?,?) ));

$sth->execute($_->[0], $_->[1], $_->[2], $_->[3]) for @rows;

$dbh->do('COMMIT');

print "et: ", time - $start, " sec\n";  
# et: 5 seconds

Update:
Setting AutoCommit => 0 as suggested by Christoforo gives the same speed improvement. As I suspected there are limits on transaction size as noted by Jenda.

[Cristoforo]

I think you could see an improvement, (I wasn't aware of the method you provided above), by setting AutoCommit to '0'. For example,

my $dbh = DBI->connect("dbi:SQLite:dbname=pedro.lite","","",
    {AutoCommit => 0}) or die "Can't connect";

Then at the end (or after 'x' amount of inserts), commit your inserts.

$dbh->commit() or die $dbh->errstr;

Chris

[Limbic~Region]

hangon,
If you are doing bulk loading from a text file, it is a bit complicated. SQLite 2.x used to have the COPY command - you can see where I used it in Fastest Rising Monks - Revisited (old ugly code - but look at Build_DB()). It disappeared in 3.x but there are a number of ways to accomplish the same thing. See this for instance (search for .import). You will need to do this through the SQLite shell. If memory serves, I once did this using DBI::Shell.

[hangon]

Thanks L~R. For some reason I was fixated on "loading" and missed the ".import" command. I installed a standalone version of SQLite to try out its command shell. Still had to process the file through Perl to get it into a suitable format, but afterwards loading it using .import was extremely fast.

[Limbic~Region]

hangon,
Still had to process the file through Perl to get it into a suitable format

I forgot to mention that the .mode command can switch to various different formats such as tabs a la

.mode tabs;
.import <file> <table>;

[Jenda]

The trick is to do things like this in less bigger transactions. One huge may be too much. Especially if the logfile you want to import gets bigger. I'm surprised you did not find any references to this, batching inserts is an "old vest". How big to make the transactions is something you usually have to find out by testing.

[andreas1234567]

You should also consider adding an index and see how that affects your performance. This simple benchmark indicates that
• Turning off autocommit roughly doubles performance.
• Turning off autocommit and adding an index roughly triples performance.
Many factors work together here, YMMV.
Read more... (7 kB)

[Anonymous Monk]

RTFM didn't help much, and I was about to post the speed question in SOPW when I found the answer on SQLite's benchmark page.
It is like DBD::mysql is not a substitute for http://mysql.com. The web is part of the manual.

[lachoy]

SQLite 3.0 has an .import command that may be useful -- see Command Line Shell For SQLite for a ref and an answer to this Stack Overflow question for an example.