Android SQLite and huge data sets

Question

We are creating an app for a client that has hundreds of megabytes of HTML in SQLite databases. We have implemented a way to query this data and scroll through it all in a reasonably fast manner. The issue is that some of the databases have very large queries (20,000+ rows) and we are seeing errors when we are growing the queries as the user is scrolling. So I guess the question is, what options do we have in querying and displaying tens of thousands of rows of data in Android?

Here is the stacktrace we're seeing:

09-10 19:19:12.575: WARN/IInputConnectionWrapper(640): showStatusIcon on inactive InputConnection 09-10 19:19:18.226: DEBUG/dalvikvm(640): GC freed 446 objects / 16784 bytes in 330ms 09-10 19:19:32.886: ERROR/CursorWindow(19416): need to grow: mSize = 1048576, size = 36, freeSpace() = 30, numRows = 17717 09-10 19:19:32.896: ERROR/CursorWindow(19416): not growing since there are already 17717 row(s), max size 1048576 09-10 19:19:32.916: ERROR/CursorWindow(19416): The row failed, so back out the new row accounting from allocRowSlot 17716 09-10 19:19:33.005: ERROR/Cursor(19416): Failed allocating fieldDir at startPos 0 row 17716 09-10 19:19:35.596: DEBUG/Cursor(19416): finish_program_and_get_row_count row 24315 09-10 19:19:41.545: DEBUG/dalvikvm(698): GC freed 2288 objects / 126080 bytes in 260ms 09-10 19:19:43.705: WARN/KeyCharacterMap(19416): No keyboard for id 0 09-10 19:19:43.717: WARN/KeyCharacterMap(19416): Using default keymap: /system/usr/keychars/qwerty.kcm.bin 09-10 19:20:04.705: ERROR/CursorWindow(19416): need to grow: mSize = 1048576, size = 17, freeSpace() = 3, numRows = 17094 09-10 19:20:04.716: ERROR/CursorWindow(19416): not growing since there are already 17094 row(s), max size 1048576 09-10 19:20:04.726: ERROR/Cursor(19416): Failed allocating 17 bytes for text/blob at 17093,2 09-10 19:20:05.656: DEBUG/Cursor(19416): finish_program_and_get_row_count row 5257 09-10 19:24:54.685: DEBUG/dalvikvm(637): GC freed 9297 objects / 524176 bytes in 247ms 09-10 19:32:07.656: DEBUG/dalvikvm(19416): GC freed 9035 objects / 495840 bytes in 199ms 

Here is our CursorAdapter code:

    private class MyAdapter extends ResourceCursorAdapter {      public MyAdapter(Context context, Cursor cursor) {         super(context, R.layout.my_row, cursor);             }      public void bindView(View view, Context context, Cursor cursor) {                         RowData data = new RowData();         data.setName(cursor.getInt(cursor.getColumnIndex("name")));          TextView tvItemText = (TextView)view.findViewById(R.id.tvItemText);         tvItemText.setText(data.getName());          view.setTag(data);     }      @Override     public Cursor runQueryOnBackgroundThread(CharSequence constraint) {         /* Display the progress indicator */         updateHandler.post(onFilterStart);          /* Run the actual query */                        if (constraint == null) {             return myDbObject.getData(null);                              }          return myDbObject.getData(constraint.toString());                     }             } 

Answer 1

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.

Answer 2

If you really need that you can also split your data and read chunks like this:

   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(); 

It's working under 2 s for 5k rows if you have indexed table.

Thanks, Arkde