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
