Bulk Insertion on Android device

user_85348 0
Viewed 40744

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?

bulkinsert sqlite android
5 Answers

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 or null.
  • 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.

Dan_Breslau_710340

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

user_13364720
Related