How to speed up cursor conversion to arraylist in Android?

Question

I am facing a performance problem when querying the data from a local database in my Andriod app. While indexing helped a lot to speed up the query itself when converting Cursor into an ArrayLists really cost time.

The code for the conversion is the following:

public MaData recordToSimpleItem(Cursor cursor) { 
  return new MaData( 
      cursor.getString(cursor.getColumnIndex(SQLiteHelper.COLUMN_ACTION)), 
      cursor.getFloat(cursor.getColumnIndex(SQLiteHelper.COLUMN_AMP)), 
      cursor.getFloat(cursor.getColumnIndex(SQLiteHelper.COLUMN_POLLUTION))
  ); 
}
................................................................
ArrayList<T> items = new ArrayList<>();
cursor.moveToFirst();
while (!cursor.isAfterLast() && !cursor.isBeforeFirst()) {
    items.add(recordToSimpleItem(cursor));
    cursor.moveToNext();
}
cursor.close();

This seems to be the standard way of getting the content from the cursor, therefore, really could not find other approaches that provide higher performance.

Right now converting a cursor with a count of 300 to an arraylist needs 2 seconds... really would be great to know if there would be a better and faster way to do this. otherwise, once the database gets bigger, there will be serious issues.

Any help would be appreciated!

Answer 1

There are some things that you can do to speed up the process.

First, calculate only once the indices of the columns in the cursor and not in every iteration of the loop and use them directly inside the while block.
This means that you should not use the method recordToSimpleItem(), which although may seem an elegant way to transform a cursor's row to a MaData object, it could delay the loop significantly.

Also, there is no reason to check isAfterLast() and cursor.isBeforeFirst() in each iteration.
All you need is to check moveToFirst() once, so that you make sure there is at least 1 row and then call cursor.moveToNext() after you process each row:

ArrayList<T> items = new ArrayList<>();

int indexAction = cursor.getColumnIndex(SQLiteHelper.COLUMN_ACTION);
int indexAmp = cursor.getColumnIndex(SQLiteHelper.COLUMN_AMP);
int indexPollution = cursor.getColumnIndex(SQLiteHelper.COLUMN_POLLUTION);

if (cursor.moveToFirst()) {
    do {
        items.add(new MaData( 
                          cursor.getString(indexAction), 
                          cursor.getFloat(indexAmp), 
                          cursor.getFloat(indexPollution)
                      )
        );
    } while (cursor.moveToNext());
}
cursor.close();