Android SQLite and Huge Data Sets

Android SQLite and huge data sets

what options do we have in querying
and displaying tens of thousands of
rows of data in Android?

You mean besides telling you that reading 20,000+ rows on a 3.5" LCD is bat-guano crazy? ;-)

It looks like CursorWindow, which is used somewhere under the covers, is having issues managing >17,000 rows. That could be one of two things:

1. You are out of heap space. With a 16MB non-compacting heap, and the fact that a Cursor holds the entire result set in the heap, that is not out of the question.
2. CursorWindow only supports 1MB of data, which is what the error message suggests more directly.

If there is a logical way to divide your queries into discrete chunks, you could do incremental queries and use CursorJoiner to stitch them together, and see if that helps.

But, in all seriousness, 20,000+ rows in a 3.5" screen, on a device that most closely resembles a 12-year-old PC in horsepower, is really asking a lot.

Dealing with a large database in Android

This is a comprehensive post on the subject (I'm not the author).

I think, overall, it needs to be communicated that SQLite is just a SQL mechanism for accessing a file. It appears that the current market limit is 50mb for the entire APK. When installing to internal memory, you require 2x your APK size. Installing to sdcard requires just the stated APK size.

Here is what you will be working against:
1.) Since SQLite is just a abstraction over your file, when you do selects, inserts, updates, etc, you will be incurring sdcard read write costs
2.) I've seen mention of a soft limit of 10000 records based on performance. This article is a bit old, so its likely gotten better.

Other then that, you'll probably have to set up some tests to see what is feasible. Cursory search of google did not show any benchmarks to date.

Android: Handling very large data sets in ContentProvider to avoid memory limits

Mobile devices are not designed to handle these amounts of data.

However, if you really want to inflict such a large scrolling list on your poor users, you can design it as a virtual list where entries are loaded only on demand; see Android Endless List.

Note: Using the OFFSET clause is inefficient; see Scrolling Cursor for details.

android application with huge database

No, applications don't just crash because they have a large database.

Part of the point of a Cursor is that it gives you a view into a large set of data, without having to load it all into memory at the same time.

If you follow best practices I see no problem - you're using a database. Forget for a second that it's on Android - you should optimize your table structure, indexes, etc, as best you can.

Also, large database or not, don't make any queries to it on the main thread. Use the Loader API if you need to show the result of a query in your UI.

Last, potentially most importantly, rethink why you even need such a large database. Is it really that common that a user will need to access all data ever while offline? Or might it make more sense for you to only store data from the last week or month, etc, and tell them that they need to be online to access older data.

Regarding your 2nd question - please in the future separate that into a separate question. But, no, storing binary blobs (images in this case) in a sqlite database is not good approach. Also, if they clear data on the app, everything is gone, so there's no advantage to using a database to avoid that. I would suggest storing images in a folder named after your app in external storage of the device, potentially storing image URIs/names in the database.

insert large data from file into android sqlite database

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.

---

Related Topics