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?

Answer 1

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

Answer 2

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.

Answer 3

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.