Android - Storing 'large' data locally

Question

TL;DR Would there be a way to store at least 23,000 records of data on app launch in an efficient manner for the user to be able to complete a form selection of this data in offline mode, if need be?

Introduction

I'm currently implementing an activity where a user can select a location from a list of countries/states, they'll have the ability to add a location if the one they need doesn't exist.

The selection of location can consist in various ways:

• The user can either search a location using the SearchView in the ActionBar which would then update the dataset inside the RecyclerView list.

• Or the user can first select a country/state (from a RecyclerView) and then a city corresponding to that country/state within another RecyclerView.

This data is grabbed from the database on the initial launch of the app (but as they're completing a form for an event, I would like them to be able to do it offline after the initial login if need be)

Dataset

So the current location DataSet consists of 23,000 records within a table with the columns as:

id | city | country | subcountry | geoID | long | lat | timezone

To query 23,000 records each time is very slow. So I was thinking there must be an easier way to use relationships to speed up the query time.

At the minute, I've stored all 23,000 records in an ArrayList<LocationModel> with the parameters of all the above columns.

Then separate them into the different categories I need for the life cycle of the Activity (i.e. countries/states and cities), but that would consist of looping through 23,000 records within the original ArrayList, then select all the countries, then get all the cities that belong to that country and then store them in a new List every time the user opens the app. I feel this is unnecessary for the performance.

I want to set the database up in a way where querying the network would not only be efficient but where only the necessary data is stored on the device within a List.

---

What are my options?

1. I was thinking I could have two different tables for countries and cities with a pointer in the cities table to the countries id.

   I could load all of the data initially on app launch with just the country data and then when the user selects the corresponding country, it'll query the city class where the relational column is pointing to the correct id.

   But I feel the user would heavily rely on network connectivity for such a menial task (after the initial app launch to query the different cities)

2. When the user first installs the app, I could get the data from the database and store it in a JSON file within the internal storage of the phone corresponding to the app, but I'm unsure if the APK size increases too much from this.

   Is it recommended to store this size of data in a file within the Assets directory or would that increase the size of the APK too much?

3. Store locally using SQL Database/Content Providers when the user first installs in the app. Again, would this affect the APK size too much?

So, my question is:

Would there be a way to store both the cities and the countries on app launch in a efficient manner for the user to be able to complete the selection of this data in offline mode if need be?

OR

Would any of the current options be a viable solution bearing in mind on APK size and comparable performance between the three?

---

Update: Decided to use Realm in the end. As it's faster than SQLlite and lightweight with APK size for the menial task I wish to complete.

Answer 1

The SQLite is binary format that is relatively fast. Use SQLite or Realm. Imho, Realm (based on SQLite) engine might be better in your case (it faster that pure SQLite).

Realm vs Sqlite for mobile development

Nowadays there is a Firebase Database from Google. Imho, you can you this format too because of it has both server and offline interactions. Because of both SQLite formats must be stored in APK file. But in order to work inside the application it is needed that sqlite-file have to be copied from assets (or raw) directory into database (workable) directory. That's why all data would be dublicated(!). Firebase Database do not have this disadvantage.

https://firebase.google.com/docs/database/android/start/

JSON and XML formats are too huge (and memory and machine time consumed) to work with them.

Oh. forgot! it is possible to integrate your code directly inside the code. Just create a class to work:)

Answer 2

Use SQLite to store the data...