Bulk paste on Android device

Question

I want to insert about 700 entries into the Android database for my next update. What is the most effective way to do this? From various posts, I know that if I use Insert statements, I have to wrap them in a transaction. There is also a message about using your own database, but I need this data to go to the standard Android database for Android. Please note that this will be done only once for each device.

Some ideas:

• Put a bunch of SQL statements in a file, read them in a line at a time, and execute SQL.

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

• Find out how to import and perform a single import of the entire file.

• Create a sqlite database containing all the records, copy it to your Android device and somehow merge the two databases.

• [EDIT] Put all the SQL statements in one file in res / values ​​as one big line. Then read their line at a time and execute SQL.

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

Answer 1

I do not believe that there is a possible way to execute # 3 or # 4 on your list.

Of the other solutions, you will list two in which the data file contains direct SQL and the other contains data in a format other than SQL.

All three will work just fine, but the last sentence to grab data from a formatted file and building the SQL file seems to be the cleanest. If a new batch update feature is added later, your data file can still be used, or at least easily converted to a usable form. In addition, creating a data file is simpler and less error prone. Finally, the presence of raw data will allow you to import into other data storage formats.

In any case, you should (as you mentioned) wrap the insertion groups in the transaction to avoid creating a transaction log for each row.

Answer 2

Usually, every time db.insert() , SQLite creates a transaction (and the resulting log file in the file system), which slows down.

If you use db.beginTransaction() and db.endTransaction() , SQLite creates only one log file in the file system and then commits all the inserts at a time, greatly speeding up the work.

Here are a few pseudo codes: Batch insert into 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 want to abort a transaction due to an unexpected error or something else, just db.endTransaction() without first setting up the transaction ( 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

Answer 3

I found that for bulk inserts, the class (obviously underused) of DatabaseUtils.InsertHelper is several times faster than when using SQLiteDatabase.insert .

Two other optimizations also helped me in the performance of my application, although they may not be acceptable in all cases:

• Do not bind values ​​that are empty or null .
• If you can be determined to be safe for this, temporarily disabling internal database locks can also help in performance.

I have a blog post with more details.

Answer 4

Well, my solution for this is weird, but it works fine ... I collect a large amount of data and insert it at a time (bulk insert?)

I use the db.execSQL(Query) command, and I am db.execSQL(Query) "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 creating a request, which can be messy. I hope this helps

Answer 5

This example below will work great

  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();