2

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.

Bradley Wilson
  • 1,197
  • 1
  • 13
  • 26

2 Answers2

2

Use SQLite to store the data...

KKSINGLA
  • 1,284
  • 2
  • 10
  • 22
  • Thanks for taking the time to answer, but why SQL over the other options? what are the pros and cons? does this affect the overall size of the .APK? and why doesn't it/does it? I'm looking for information for me to know why this option is the most viable for me to learn, not to just say what I should do. If the answer is detailed enough, I have no problem starting a bounty and offering extra reputation. – Bradley Wilson Mar 02 '17 at 11:02
  • Accessing SQL is faster than every other option if you want to store data locally. App size will not increased when you build the apk but yes it will increase the size after installation and storing data. – KKSINGLA Mar 02 '17 at 11:04
0

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:)

Community
  • 1
  • 1
Vyacheslav
  • 26,359
  • 19
  • 112
  • 194
  • Thank-you for the recommendation, Note: I'm already using an SDK (parse/sashido) as a back-end. Would using the Firebase SDK conflict with this? Would it be better to just use SQL and sacrifice the small size increase on the APK rather than have two database libraries compiled? – Bradley Wilson Mar 02 '17 at 11:23
  • @BradleyWilson , due to parse.com was changed to opensource project I do not think that Facebook will develop this project in future (imho). So , I do not use parse. I do not know about any conflict that would appear. If your database is not huge - it is not necessary to bear in mind the final side of APK. It would be your choice. I do not know what do you want to do, your app, the values of the sizes, etc. – Vyacheslav Mar 02 '17 at 11:32
  • Sashido is the new alternative after Parse.com got shutdown, so it's still valid. I'll have a go at using SQL and see the differences in performance, thanks for the advice. – Bradley Wilson Mar 02 '17 at 11:33
  • 1
    @BradleyWilson , in my case I've used pure sqlite 26mb database inside APK file. Nobody said about 60mb of application the real device – Vyacheslav Mar 02 '17 at 11:33
  • @BradleyWilson , confirm if it was helpful so – Vyacheslav Mar 02 '17 at 11:34