I'm currently working on an application for the iPhone/iPod touch and decided to use SQLite as storage for the data. The app will display medical eponyms and must fill the database on first launch and when new eponym data is available from an XML file. It parses the XML file and fills the SQLite database, which currently results in 27 entries to a categories table, 1'623 entries to the eponyms table and 3'281 entries to a linker table (4'931 entries in total). The whole database weighs only 394 KB.

Problem

My first approach was to create the empty database file, create the tables and insert all the data with a simple INSERT statement on every row. This way the database file had to grow some bytes after every insert, which turned out not to be too effective. In the iPhone Simulator on my Mac it took about 22 seconds to parse the XML and insert the 4'931 entries into the three tables, on my iPod touch it took way longer than a minute.

Solution

So I started looking for alternatives. Providing the complete .sqlite - file was not really an option since I wanted to allow updating only the eponyms without having to update the whole app and downloading the XML is more transparent.
The solution is to first use an in-memory database and then bulk-copy all its data to a file-database using INSERT INTO table SELECT * FROM other_table. This results in much faster database creation than the old fashioned way: In the Simulator on my Mac it now takes 300 milliseconds to parse + insert the entries into the RAM-database and another 90 milliseconds to bulk-copy using the before-mentioned statement, on the iPod touch the parsing + insert takes less than 11 seconds and the bulk insert takes about 500 milliseconds.

Device	Direct Insert	RAM Database + Bulk Insert
iPhone Simulator	22 sec	300 ms + 90 ms
iPod touch 1st Gen	>> 1 min	11 sec + 500 ms

How-To

With SQLite, you can open a database in the RAM when using :memory: as the database path. I create the file database like before by touching an empty file, opening it as a SQLite database and creating the tables:

(Note that sqlite3_open_v2([sqlPath UTF8String], &database, SQLITE_OPEN_CREATE, NULL) seems not to be available on the iPhone OS.)
After that I create a database in RAM with exactly the same tables:

I now use the memory_database handle to execute the same INSERT statements like before on the file database which is much much faster, and after that copy all data from memory_database to the file database which seems to not take place incrementally but in a quick swoop (90 ms vs 22 sec). This is done by attaching the file database to the memory database with ATTACH DATABASE "path/to/database.sqlite" AS database_alias first and then using the INSERT INTO ... SELECT FROM statement:

The Application named Eponyms will be available on the iTunes Store once it's ready, and since it will be an Open Source App you can find the source at the Google Code Project Website: eponyms-touch.googlecode.com/

Edit: I should have added this a long time ago - if you BEGIN TRANSACTION before all the insert statements and call COMMIT after you've finished, the process of writing directly to your database file will be about as fast as if using the memory approach. I switched to using the transaction view.