This page last modified: Sep 01 2009
title:SQLite hints and Perl DBI mini-howto. keywords:sqlite,sql,database,dbi,dbd,perl,postgres,postgresql,install,upgrade,db,feature,advanced,html,export,table,spreadsheet,date,conversion,unix description:Speed hints, examples SQLite commands, and Perl DBI/DBD examples. Table of contents ----------------- Docs link to sqlite3 command line interface (CLI) SQLite3 import speed hints Install SQLite and Perl DBI/DBD How to check Perl DBI and Perl DBD drivers Perl and SQL example Docs link to sqlite3 command line interface (CLI) ------------------------------------------------- I've occasionally had trouble finding the SQLite sqlite3 command line docs: http://www.sqlite.org/sqlite.html It isn't a client, but is analagous to PostgreSQL psql client CLI. SQLite3 import speed hints -------------------------- 1) Use a pre-existing db file large enough for your db. SQLite expands the database somewhat slowly, and it does not seem to try to predict how much space you might need. It would be tricky to see into the future. The simple solution is to create a database with a dummy table such that the SQLite database is large enough to hold your data. Drop the table and do not vacuum the database. Copy the now empty database file and insert into it. Too large is better than too small, assuming you've got plenty of disk. It will take a while to initially expand the database, but you only need to do that once (or only need to do it occasionally). This assumes that your import tasks involve a known quantity of data. 2) Use begin/commit around the all the SQL, or at least around maybe every 100K insert statements. Notice that SQLite database dumps are wrapped in a transaction. The first line of the dump is "begin transaction;" and the last line is "commit;" In addition to the usual sanity gained by using a transaction, this greatly improves speed. In some cases the speed improvement is greater than 5000 times. When sqlite3 runs SQL not wrapped in a transaction, each statement will be treated as a transaction. Each statement incurs the commit overhead. 3) Modify the .sql by putting a select 'xx inserts done'; every 10000 inserts (more or less) to give you an idea of the file import status. I wrote a small Perl script that inserts a select after every few thousand insert statements. The Perl script counts the inserts, and the select serves merely to get SQLite to print a message to stdout. See the code below item 4. 4) As of 2009 and SQLite 3.6.12, sqlite3 can do 1217874 inserts in 34 seconds on a middle of the road server using the suggestions above. Usage: cat dump.sql | sqlite_add_status.pl > dump_status.pl -- #!/usr/bin/perl # sqlite_add_status.pl This probably works with most SQL. Naively # assumes that "insert" is at the beginning of the line and followed # by some whitespace and "into". use strict; # Add a select status statement every $freq number of inserts. my $freq = 50000; main: { # Read in a .sql file. Don't change any exising lines, but insert # a status select statement after every $freq insert statements. my $insert_count = 0; while(my $line = <>) { print $line; if ($line =~ m/^insert\s+into/i) { $insert_count++; if (($insert_count % $freq) == 0) { print "select '$insert_count done';\n"; } } } } -- Install SQLite and Perl DBI/DBD ------------------------------- For Fedora Core 4, perl-DBD-SQLite is in the "extras" repository. yum -y install perl-DBD-SQLite The cpan page says the Perl DBD module includes a full sqlite distro. The documentation is at: http://search.cpan.org/~msergeant/DBD-SQLite-1.13/lib/DBD/SQLite.pm SQL syntax: http://www.sqlite.org/lang.html sqlite command line: http://www.sqlite.org/sqlite.html sqlite3 mydate.db .read schema.sql SQLite has no date or time data types. If you need dates, I strongly suggest that you use PostgreSQL. How to check Perl DBI and Perl DBD drivers ------------------------------------------ Here is a Perl script drivers.pl. Run this to determine which Perl DBI drivers are present on your system: #!/usr/bin/perl use DBI; @driver_names = DBI->available_drivers(); print "driver_names (apparently installed, available):\n"; foreach my $dn (@driver_names) { print "$dn\n"; } Perl and SQL example -------------------- Here is a trivial example of a table schema SQL file suitable for use with SQLite via the .read command. I recomment that you put your table creation commands as well as everything else necesary to create the "empty" database into a file named schema.sql create table query ( query_id integer, query_name text, query_date text ); Run the SQLite command line (sqlite3) and enter the .read command as follows: sqlite3 demo.db .read schema.sql Below is a tiny example of Perl and SQLite, with transactions, inserting data into a database. The file demo.db contains the database. #!/usr/bin/perl use DBI; use strict; main: { my $dbargs = {AutoCommit => 0, PrintError => 1}; my $dbh = DBI->connect("dbi:SQLite:dbname=demo.db","","",$dbargs); $dbh->do("insert into query (query_id, query_name,query_date) values (1,'stuff','2006-11-06')"); if ($dbh->err()) { die "$DBI::errstr\n"; } $dbh->commit(); $dbh->disconnect(); }