Android, how to request a huge database in android (cursor size is limited to 1 MB)

Question

I am working on creating an application that needs to be requested at some point, a database with more than 4k rows, and each row has 90 fields (rows). The problem is that if I select * from the database, my cursor will become really large (more than 4 MB). And the cursor in android is limited to 1 MB. How can I solve this, or what is the most elegant way to get around this? Can I break the database into smaller pieces and query them?

Thanks Arkde

Answer 1

I have found a way to handle this, and I want to share with everyone who needs it.

int limit = 0; while (limit + 100 < numberOfRows) { //Compose the statement String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ limit+"', 100"; //Execute the query Cursor cursor = myDataBase.rawQuery(statement, null); while (cursor.moveToNext()) { Product product = new Product(); product.setAllValuesFromCursor(cursor); productsArrayList.add(product); } cursor.close(); limit += 100; } //Compose the statement String statement = "SELECT * FROM Table ORDER someField LIMIT '"+ (numberOfRows - limit)+"', 100"; //Execute the query Cursor cursor = myDataBase.rawQuery(statement, null); while (cursor.moveToNext()) { Product product = new Product(); product.setAllValuesFromCursor(cursor); productsArrayList.add(product); } cursor.close(); 

The basic idea is to split your data so that you can use the cursor the way it should be used. It works for 2 seconds for 5k rows if you specified a table.

Thanks Arkde

Answer 2

Ian P is absolutely right. To expand on his answer, if you have 90 fields, you probably aren't using the most effective strategies. You need to have several tables that you associate by ID. You should NEVER just iterate over each variable that may or may not be needed in a single table. That is why you are oversizing. Even if you just copied a massive Excel spreadsheet with 90 columns, you should only design to get a few at a time. The mobile user will absolutely NOT wait for this request. They will not. @ 3G, you require LOT from your potential users. Not only do you ask them to sit and wait until they literally see nothing for ~ 5 minutes, you also essentially require precious battery life.

1MB is great. If you retrieve data as needed and use effective coding strategies, 1MB will be more than you will ever need.

Answer 3

Do you need all these lines at once? Can you get them in parts? This question has been asked several times: Android SQLite and huge datasets

Here's another suggestion: if you have 90 fields that you need to change, divide them into 10 different views. Each image has a left arrow and a right arrow so you can move horizontally across the screens. Therefore, each view displays 9 fields. Or some kind of strategy. In essence, these are all the same views, with the exception of column names, so you don't need to change a lot of code.

Answer 4

Well, as a rule, you never choose *. To begin with, each row will have a unique identifier, and your user will want to select only certain rows and columns - that is, what they can see on the android screen. Without seeming rude, this is a pretty simple question. You return only the columns and rows that you want to display for this screen on the phone - otherwise you consume unnecessary battery life so that the data is never crossed out. the standard approach is to use parameterized stored procedures. Google parameterizes the stored procedures and does a little reading - by mistake you cannot update any table, you return a unique row identifier for this table.