Android SQLite and huge data sets

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

Solution 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.

Solution 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