Android: Handling very large data sets in ContentProvider to avoid memory limits

Question

I am using a ContentProvider to query a database and return a Cursor that is used in a CursorLoader:

ItemsActivity:

public class ItemsActivity extends SherlockActivity implements LoaderCallbacks<Cursor> {

    @Override
    public void onCreate(Bundle savedInstance) {
        ....
        getSupportLoaderManager().initLoader(LOADER_ID, null, this);
        ...
    }

    @Override
    public Loader<Cursor> onCreateLoader(int loaderId, Bundle bundle) {
        return new CursorLoader(getApplicationContext(), itemsListUri, ...); 
    }

    ...
}

ItemsContentProvider:

public Cursor query(Uri uri, String[] projection, String selection, ...) {
    SqliteQueryBuilder builder = new SqliteQueryBuilder();
    builder.setTables(ItemsTable.NAME);
    return builder.query(db, projection, selection, ...);
}

The activity has a ListView, and I am using a CursorAdapter (updated via the LoaderCallbacks) to represent the data within the cursor.

This is working fine, until I need to lookup the items in a large data set (for example, over 30,000 rows). Observing the logs I see that the lookup exceeds memory limits and some rows are dropped from the resulting cursor.

My question: what is the best way of handling very large datasets when using cursors like this?

My current solution is to break up the SQLite query in the ContentProvider into a sequence of queries with offsets and limits, then combine these queries using the MergeCursor class:

private static final int LIMIT = 5000;

// Ignoring projection, selection, etc for simplicity here
public Cursor query(Uri uri, String projection, String selection, ...) {
  List<Cursor> cursors = newList();
  int offset = 0;
  Cursor c = db.rawQuery("select * from items limit " + LIMIT + " offset " + offset, null);
  while (c.getCount() > 0) {
    cursors.add(c);
    offset += c.getCount();
    c = db.rawQuery("select * from items limit " + LIMIT + " offset " + offset, null);
  }
  return createMergedCursor(cursors);
}

private Cursor createMergedCursors(List<Cursor> cursors) {
    if (cursors.size() == 1) {
        return cursors.get(0);
    }
    return new MergeCursor(toCursorsArray(cursors));
}

This will load all the data, but there's a long delay while doing the lookups for the first time. The list view is empty for about 5 seconds while multiple queries are performed.

Note that when I try a single lookup (rather than batched lookups), the loading is almost instantaneous, although there are slight pauses when scrolling the list as memory limits are reached.

So:

Using a single query: fast list view updating, but scrolling pauses and memory limits reached.

Using batched queries: slow list view updating, but scrolling is smooth and no memory limits reached.

I'm wondering if there's a better solution that will update the list view quickly, but will also fetch more data as required when scrolling the list.

Android 4.2.1, Nexus 7

Answer 1

Mobile devices are not designed to handle these amounts of data.

However, if you really want to inflict such a large scrolling list on your poor users, you can design it as a virtual list where entries are loaded only on demand; see Android Endless List.

Note: Using the OFFSET clause is inefficient; see Scrolling Cursor for details.