Android how to query huge database in android (cursor size is limited to 1MB)

Question

I'm working to develop an application that has to query at some time, a database with over 4k rows, and each row has 90 fields (Strings). The problem is that if I select * from database, my cursor gets really big (over 4MB). And the cursor in android is limited to 1MB.

How can I solve this, or what's the most elegant method to workaround this? It is possible to split database in smaller chunks and query them out?

score 12 · Accepted Answer · answered Mar 27 '12 at 07:16

I found a way to handle this and I want to share with all who need 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 main idea is to split your data, so you can use the cursor as it should be used. It's working under 2 s for 5k rows if you have indexed table.

Thanks, Arkde

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

Do you need all these rows at the same time? Can you fetch them in parts? This question has been asked several times: Android SQLite and huge data sets

Here's one more suggestion: If you have 90 fields that you need to modify, split them into 10 different views. On each view have a left arrow and right arrow so you can horizontally traverse across screens. Hence each view will show 9 fields. Or some strategy like that. Essentially these are all the same views except for column names so you shouldn't have to modify much code.

edited May 23 '17 at 12:09

Community

1
1

answered Mar 26 '12 at 14:23

Sid

7,511
2
28
41

Yes I need all these rows at the same time, since I need them stored in an ArrayList to fill and adapter for a listview. And the problems seems to be with the size of objects stored in cursor not with the number of rows (in the question answered in your suggested link, there is a problem with the number of rows) – Aurelian Cotuna Mar 26 '12 at 14:28
The limit applies to the overall data, whether it be due to a large number of rows or really large columns or many columns. I don't know of any alternative but to compact your data somehow. – Sid Mar 26 '12 at 14:32
Any idea how can I compact my data? I used String to store them, since the string size grows with the number of characters inside it... I don't know any other way to optimize this. – Aurelian Cotuna Mar 26 '12 at 14:35
Do you need all 90 fields at the same time? Can't you have separate views? Are you using CursorAdapter? – Sid Mar 26 '12 at 14:46

score 1 · Answer 3 · answered Mar 26 '12 at 14:27

1

Well as a rule you never do select *. For a start each row will have a unique identifier, and your user will want to select only certain rows and columns - ie what they can see on an android screen. Without appearing to be rude this is a pretty basic question. You only return the columns and rows you want to display for that screen on the phone - otherwise you consume unnecssary battery life transfering never to be diaplayed data. the standard approach is to used parameterised stored procedures. Google parameterised stored procedures and do a little reading - by the by - you cant update any table unlees you return the unique row identifier for that table.

answered Mar 26 '12 at 14:27

Ian P

1,724
1
10
12

I have to do a select* because, unfortunately, I need all the fields to be used. The user will edit some of the fields, and others will be used for different settings. – Aurelian Cotuna Mar 26 '12 at 14:33
In that case I'd have dynamic SQL where the field names are generated by the user selecting them from a check box list, plus any hidden fields you need to update, then go to a detail view. You can also do a dynamic update statement. I still dont know how you get all that data on a typical android display though. – Ian P Mar 27 '12 at 15:42

Android how to query huge database in android (cursor size is limited to 1MB)

3 Answers3

Linked

Related