sqlite - Bulk Insertion on Android device

Question

I want to bulk insert about 700 records into the Android database on my next upgrade. What's the most efficient way to do this? From various posts, I know that if I use Insert statements, I should wrap them in a transaction. There's also a post about using your own database, but I need this data to go into my app's standard Android database. Note that this would only be done once per device.

Some ideas:

1. Put a bunch of SQL statements in a file, read them in a line at a time, and exec the SQL.

2. Put the data in a CSV file, or JSON, or YAML, or XML, or whatever. Read a line at a time and do db.insert().

3. Figure out how to do an import and do a single import of the entire file.

4. Make a sqlite database containing all the records, copy that onto the Android device, and somehow merge the two databases.

5. [EDIT] Put all the SQL statements in a single file in res/values as one big string. Then read them a line at a time and exec the SQL.

What's the best way? Are there other ways to load data? Are 3 and 4 even possible?

Answer 1

Normally, each time db.insert() is used, SQLite creates a transaction (and resulting journal file in the filesystem), which slows things down.

If you use db.beginTransaction() and db.endTransaction() SQLite creates only a single journal file on the filesystem and then commits all the inserts at the same time, dramatically speeding things up.

Here is some pseudo code from: Batch insert to SQLite database on Android

try
{
  db.beginTransaction();

  for each record in the list
  {
    do_some_processing();

    if (line represent a valid entry)
    {
      db.insert(SOME_TABLE, null, SOME_VALUE);
    }

    some_other_processing();
  }

  db.setTransactionSuccessful();
}
catch (SQLException e) {}
finally
{
  db.endTransaction();
}

If you wish to abort a transaction due to an unexpected error or something, simply db.endTransaction() without first setting the transaction as successful (db.setTransactionSuccessful()).

Another useful method is to use db.inTransaction() (returns true or false) to determine if you are currently in the middle of a transaction.

Documentation here