0

I have around 9000 json records with 7-8 attributes stored in file. I have to insert that data into sqlite database. What is the most efficient way to do so.
So far, I am converting that file data into jsonarray and convert that into arraylist and then insert that list into database, which definitely doesn't seem to be an effective way

Edit :- this file already exists in resources folder, so the data needs to be inserted in the database at the beginning

Abhishek Aggarwal
  • 207
  • 2
  • 4
  • 13
  • 1
    use Realm database. – Aks4125 Feb 05 '18 at 12:00
  • Or [Objectbox](http://objectbox.io/) – Kai Feb 05 '18 at 12:01
  • https://www.androidhive.info/2016/05/android-working-with-realm-database-replacing-sqlite-core-data/ – Arya Feb 05 '18 at 12:04
  • i wish i could do that, but i'm already using around 20 more tables in that database – Abhishek Aggarwal Feb 05 '18 at 12:09
  • i tried loading data using above mentioned way, and it took around 6-8 minutes – Abhishek Aggarwal Feb 05 '18 at 12:11
  • 1
    "it took around 6-8 minutes" -- if you provide a [mcve], perhaps somebody can offer suggestions for optimizing it (e.g., use transactions). If this data is supposed to ship with your app, don't ship JSON, but instead ship the prepared SQLite database and [use `SQLiteAssetHelper`](https://github.com/jgilfelt/android-sqlite-asset-helper) to use that packaged database. – CommonsWare Feb 05 '18 at 12:23
  • 1
    Instead of converting Json -> JsonArray -> ArrayList -sqlite, use Json-> JsonArray and directly put to sqlite database one by one – Onkar Feb 05 '18 at 13:23
  • I put logs to get the exact time taken in each process. To be exact, (file -> jsonarray : 9 seconds), (jsonarray -> arraylist : 33 seconds) and (arraylist -> insert completion into database : 3 minutes 2 seconds) – Abhishek Aggarwal Feb 05 '18 at 14:37
  • Thanks CommonsWare, i used transactions and it took only 13 seconds to insert 9000 records – Abhishek Aggarwal Feb 06 '18 at 06:48
  • @CommonsWare, Please post your comment, I will mark your comment as the right answer. – Abhishek Aggarwal Jun 06 '18 at 13:08

1 Answers1

1

Do not ship JSON with the app, but instead ship a SQLite database with the app.

For cases where the JSON is coming from some other place (e.g., a Web service), make sure that you use transactions when updating SQLite with that data. By default, each SQL operation is a single transaction, and transactions are a bit expensive. It is far faster to insert 9000 items in one transaction (or 9 transactions, or 90 transactions) than it is to insert 9000 items in 9000 transactions. If you are using SQLiteDatabase, use beginTransaction(), markTransactionSuccessful(), and endTransaction(). If you are using Room, use @Transaction. If you are using some other database API, consult its documentation for how to set up your own transactions.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491