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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With