A little trick to display a large amount of data in a ListView

In this article, I want to share a recently found solution that allows you to display, and most importantly, easily scroll large amounts of data in a standard ListView.

Problem

The standard mechanism for displaying lists from a database in Android looks like this:

• Activity contains a ListView
• ListView accesses an instance of CursorAdapter.
• CursorAdapter retrieves data from an object that implements the Cursor interface.
• Cursor obtained either from ContentProvider, or directly from SQLiteDatabase

')
Everything works fine exactly yes as long as the number of rows in the Cursor is relatively small. But if there are 50 thousand, 100 thousand and more lines in it (although the matter is not only in the number of lines, but more on that later), the list will be slowed down from time to time. This is especially noticeable in the "fast scrolling" if the ListView is set to true, the fastScrollEnabled property.

Leaving behind the brackets, why do we still need to put such a huge amount of data in the ListView. We will consider this a requirement of the customer, which we are not able to influence. We will also consider impossible workflows with preloaders in the spirit of Twitter and “endless lists”, the next piece of data in which is loaded when the end of the already loaded data is reached.

We need to be able to scroll to any of the one hundred thousand list items at any time without noticeable interface hangs. How to do it? Let's try, for a start, to find the cause of the brakes.

Cause

We will not consider the ViewHolder - I assume that any more or less competent android-developer knows and uses this pattern. On the inadmissibility of creating a large number of objects in the getView method due to the inevitability of retribution in the face of the garbage collector, I will also keep silent .

We are interested in the work of the cursor to the database.

Cursor, which we get from SQLiteDatabase , is an instance of the SQLiteCursor class, which inherits from AbstractWindowedCursor . This class, in turn, contains a copy of CursorWindow .

In the last class is our problem. If you take a look at the source code for CursorWindow , you will see that the window size is limited by a constant named com.android.internal.R.integer.config_cursorWindowSize. The user interface slows down exactly at the moment when the space in the window ends (not only the number of rows in the sample matters, but also the length of each row), and AbstractWindowedCursor requests data for the new window and then copies it to this window.

You can, of course, try to increase the size of the window. But this is, firstly, a bad decision, since it does not eliminate the problem, but only postpones it. Secondly, we cannot increase it all the time, since the memory of the device is limited. And thirdly, it is technically unreasonably difficult.

We will go another way.

Decision

Generally speaking, SQLite is a fairly fast database, and most of the “brakes” are caused by its incorrect use. Especially fast it works when querying the primary key.

The idea is as follows: we only request primary keys, and then, when displaying each of the rows, we query the remaining columns for that primary key. And it actually works faster.

To illustrate this idea, I wrote my own implementation of the BaseAdapter class.

package me.ilich.fastscroll; import android.content.Context; import android.database.Cursor; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; public abstract class QuickAdapter extends BaseAdapter { private final DataSource mDataSource; private int mSize = 0; private Cursor mRowIds = null; private final Context mContext; public QuickAdapter(Context context, DataSource dataSource){ mDataSource = dataSource; mContext = context; doQuery(); } private void doQuery(){ if(mRowIds!=null){ mRowIds.close(); } mRowIds = mDataSource.getRowIds(); mSize = mRowIds.getCount(); } @Override public int getCount() { return mSize; } @Override public Object getItem(int position) { if(mRowIds.moveToPosition(position)){ long rowId = mRowIds.getLong(0); Cursor c = mDataSource.getRowById(rowId); return c; }else{ return null; } } @Override public long getItemId(int position) { if(mRowIds.moveToPosition(position)){ long rowId = mRowIds.getLong(0); return rowId; }else{ return 0; } } @Override public View getView(int position, View convertView, ViewGroup parent) { mRowIds.moveToPosition(position); long rowId = mRowIds.getLong(0); Cursor cursor = mDataSource.getRowById(rowId); cursor.moveToFirst(); View v; if (convertView == null) { v = newView(mContext, cursor, parent); } else { v = convertView; } bindView(v, mContext, cursor); cursor.close(); return v; } public abstract View newView(Context context, Cursor cursor, ViewGroup parent); public abstract void bindView(View view, Context context, Cursor cursor); public interface DataSource { Cursor getRowIds(); Cursor getRowById(long rowId); } } 

To use this class, you need to implement the newView and bindView methods in the same way as it is done for the CursorAdapter, and also write the implementation of QuickAdapter.DataSource, like so:

  class MyDataSource implements QuickAdapter.DataSource { @Override public Cursor getRowIds() { return mDatabase.rawQuery("SELECT rowid FROM table1", new String[]{}); } @Override public Cursor getRowById(long rowId) { return mDatabase.rawQuery("SELECT * FROM table1 WHERE rowid = ?", new String[]{Long.toString(rowId)}); } } 

Conclusion

On the Samsung Galaxy Tab 10.1 without any noticeable brakes worked "fast scrolling" for a list of 300 thousand items, each of which up to 2Kb. Standard CursorAdapter also braked so that it was scary to watch.

For the CursorAdapter, the maximum delay for calling the getView method was 553 ms, for QuickAdapter, 47 ms. Performance measurements were performed using the following code:

  @Override public View getView(int arg0, View arg1, ViewGroup arg2) { long t1 = System.currentTimeMillis(); View result = super.getView(arg0, arg1, arg2); long t2 = System.currentTimeMillis(); long dt = t2-t1; if(dt>10){ Log.i("QuickAdapter", dt+""); } return result; } 

The idea outlined here is taken from the article Non-standard approach to "improving the performance" of select queries in SQLite by Sergey Slavin . I would also like to say thanks to my colleague, Dmitry Tukhtamanov, who several months ago implemented the same approach for iOS.

^{Picture taken from here .}