Bulk Insertion on Android device
AndroidSqliteBulkinsertAndroid Problem Overview
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:
-
Put a bunch of SQL statements in a file, read them in a line at a time, and exec the SQL.
-
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()
. -
Figure out how to do an import and do a single import of the entire file.
-
Make a sqlite database containing all the records, copy that onto the Android device, and somehow merge the two databases.
-
[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?
Android Solutions
Solution 1 - Android
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.
Solution 2 - Android
I've found that for bulk insertions, the (apparently little-used) DatabaseUtils.InsertHelper class is several times faster than using SQLiteDatabase.insert
.
Two other optimizations also helped with my app's performance, though they may not be appropriate in all cases:
- Don't
bind
values that are empty ornull
. - If you can be certain that it's safe to do it, temporarily turning off the database's internal locking can also help performance.
I have a blog post with more details.
Solution 3 - Android
This example below will work perfectly
String sql = "INSERT INTO " + DatabaseHelper.TABLE_PRODUCT_LIST
+ " VALUES (?,?,?,?,?,?,?,?,?);";
SQLiteDatabase db = this.getWritableDatabase();
SQLiteStatement statement = db.compileStatement(sql);
db.beginTransaction();
for(int idx=0; idx < Produc_List.size(); idx++) {
statement.clearBindings();
statement.bindLong(1, Produc_List.get(idx).getProduct_id());
statement.bindLong(2, Produc_List.get(idx).getCategory_id());
statement.bindString(3, Produc_List.get(idx).getName());
// statement.bindString(4, Produc_List.get(idx).getBrand());
statement.bindString(5, Produc_List.get(idx).getPrice());
//statement.bindString(6, Produc_List.get(idx).getDiscPrice());
statement.bindString(7, Produc_List.get(idx).getImage());
statement.bindLong(8, Produc_List.get(idx).getLanguage_id());
statement.bindLong(9, Produc_List.get(idx).getPl_rank());
statement.execute();
}
db.setTransactionSuccessful();
db.endTransaction();
Solution 4 - Android
Well, my solution for this it kind of weird but works fine... I compile a large sum of data and insert it in one go (bulk insert?)
I use the db.execSQL(Query)
command and I build the "Query" with the following statement...
INSERT INTO yourtable SELECT * FROM (
SELECT 'data1','data2'.... UNION
SELECT 'data1','data2'.... UNION
SELECT 'data1','data2'.... UNION
.
.
.
SELECT 'data1','data2'....
)
The only problem is the building of the query which can be kind of messy. I hope it helps
Solution 5 - Android
I don't believe there is any feasible way to accomplish #3 or #4 on your list.
Of the other solutions you list two that have the datafile contain direct SQL, and the other has the data in a non-SQL format.
All three would work just fine, but the latter suggestion of grabbing the data from a formatted file and building the SQL yourself seems the cleanest. If true batch update capability is added at a later date your datafile is still usable, or at least easily processable into a usable form. Also, creation of the datafile is more straightforward and less error prone. Finally, having the "raw" data would allow import into other data-store formats.
In any case, you should (as you mentioned) wrap the groups of inserts into transactions to avoid the per-row transaction journal creation.