Using Cursor with ListView adapter for a large amount of data

Question

i am using a custom CursorAdapter to get data from a SQLite database and show it in a listview. The database contains a 2 columns with about 8.000 rows. so i am looking for a way to query and show all the data as fast as possible. i have done this with asyncTask here is the code:

private class PrepareAdapter extends AsyncTask<Void,Void,CustomCursorAdapter > {

 @Override
 protected void onPreExecute() {
     dialog.setMessage("Wait");
     dialog.setIndeterminate(true);
     dialog.setCancelable(false);
     dialog.show();


     Log.e("TAG","Posle nov mAdapter");
 }

 @Override
 protected CustomCursorAdapter doInBackground(Void... unused) {

   Cursor cursor =  myDbNamesHelper.getCursorQueryWithAllTheData();
   mAdapter.changeCursor(cursor);
   startManagingCursor(cursor);
   Log.e("TIME","posle start managing Cursor" + String.valueOf(SystemClock.elapsedRealtime()-testTime)+ " ms");
     testTime=SystemClock.elapsedRealtime();

     mAdapter.initIndexer(cursor);
     return mAdapter;
 }

 protected void onPostExecute(CustomCursorAdapter result) {

     TabFirstView.this.getListView().setAdapter(result);
     Log.e("TIME","posle adapterSet" + String.valueOf(SystemClock.elapsedRealtime()-testTime)+ " ms");
    testTime=SystemClock.elapsedRealtime();
     dialog.dismiss();
 }

}

this works good except the part when i need to set the result into an adapter. i have done some time tests and it takes aprox 700 ms to make it past the startManagingCursor. the problem is that it takes about 7 seconds to make it past the setAdapter(result) and this is running in UI thread so it makes my app unresponsive (the progress dialog freezes and sometimes does the app). how do i make this time less? can i make this also run in background or any way to increase responsiveness?

tnx.

public class CustomCursorAdapter extends SimpleCursorAdapter implements OnClickListener,SectionIndexer,Filterable,
                                    android.widget.AdapterView.OnItemClickListener{

    private Context context;
    private int layout;
    private AlphabetIndexer alphaIndexer;

    public CustomCursorAdapter (Context context, int layout, Cursor c, String[] from, int[] to) {
        super(context, layout, c, from, to);
        this.context = context;
        this.layout = layout;

    }
    public void initIndexer(Cursor c){
         alphaIndexer=new AlphabetIndexer(c, c.getColumnIndex(DataBaseNamesHelper.COLUMN_NAME), " ABCDEFGHIJKLMNOPQRSTUVWXYZ");
    }

    @Override
    public View newView(Context context, Cursor cursor, ViewGroup parent) {

        Cursor c = getCursor();

        final LayoutInflater inflater = LayoutInflater.from(context);
        View v = inflater.inflate(layout, parent, false);

        int nameCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_NAME);

        String name = c.getString(nameCol);

        /**
         * Next set the name of the entry.
         */
        TextView name_text = (TextView) v.findViewById(R.id.name_entry);
        if (name_text != null) {
            name_text.setText(name);
        }

        int favCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_FAVOURITED);
        int fav = c.getInt(favCol);

        int idCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_ID);

        Button button = (Button) v.findViewById(R.id.Button01);
        button.setOnClickListener(this);
        button.setTag(c.getInt(idCol));
        if(fav==1){
            button.setVisibility(View.INVISIBLE);
        }
        else button.setVisibility(View.VISIBLE);

        return v;
    }

    @Override
    public void bindView(View v, Context context, Cursor c) {

        int nameCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_NAME);

        String name = c.getString(nameCol);

        /**
         * Next set the name of the entry.
         */
        TextView name_text = (TextView) v.findViewById(R.id.name_entry);
        if (name_text != null) {
            name_text.setText(name);
        }
        int favCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_FAVOURITED);
        int fav = c.getInt(favCol);

        Button button = (Button) v.findViewById(R.id.Button01);
        button.setOnClickListener(this);
        int idCol = c.getColumnIndex(DataBaseNamesHelper.COLUMN_ID);
        button.setTag(c.getInt(idCol));
      //  Log.e("fav",String.valueOf(fav));
        if(fav==1){
            button.setVisibility(View.INVISIBLE);
        } else button.setVisibility(View.VISIBLE);
    }



    @Override
    public int getPositionForSection(int section) {
        return alphaIndexer.getPositionForSection(section);
    }

    @Override
    public int getSectionForPosition(int position) {
          return alphaIndexer.getSectionForPosition(position);
    }

    @Override
    public Object[] getSections() {
          return alphaIndexer.getSections();
    }
    @Override
    public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
        Log.e("item Click", arg1.toString()+ " position> " +arg2);
    }

    @Override
    public void onClick(View v) {
            if(v.getId()==R.id.Button01){
                //Log.e("Button Click", v.toString()+ " position> " +v.getTag().toString());
                v.setVisibility(View.INVISIBLE);
                DataBaseNamesHelper dbNames = new DataBaseNamesHelper(context);
                dbNames.setFavouritesFlag(v.getTag().toString());
            }

        }



}

Answer 1

The reason for the slow time in loading the adapter is the internal call the CursorAdapter makes to Cursor.getCount().

Cursors in Android are lazily loaded. The results are not loaded until they are needed. When the CursorAdapter calls getCount() this forces the query to be fully executed and the results counted.

Below are a couple links discussing this very issue.

http://groups.google.com/group/android-developers/browse_thread/thread/c1346ec6e2310c0c

http://www.androidsoftwaredeveloper.com/2010/02/25/sqlite-performance/

My suggestion would be to split up your query. Load only the number of visible list items on screen. As the user scrolls load the next set. Very much like the GMail and Market applications. Unfortunately I don't have an example handy :(

This doesn't answer your question but hopefully it provides some insight :)

Answer 2

Not sure why setAdapter should take so long. I've done about 5000 rows much faster than that. However, I usually create an adapter without a cursor first, call setAdapter with the "empty" adapter, then kick off an asynchronous query using an AsyncQueryHandler subclass. Then, in onQueryComplete, I call changeCursor on the adapter.

Answer 3

Well, I can only offer you a dumb suggestion at this point - begin a query in a separate thread that will go through the entire database and create your 8000-row cursor.

In your UI thread, create a cursor where you set the limit to 100 or so, and use that for your adapter. If the user scrolls to the bottom of your list, you could add a row with a ProgressBar or indicate otherwise that there's more to come.

Once your second thread is done, replace the adapter.

Alternatively, you could have an array adapter or something similar, do a bunch of queries in the second thread with 100 rows each, and add them to your array adapter as they come in. That would also make it easier to add a dummy row at the bottom that tells users to hold their horses.

One note - I would assume it's safe to read from the database from two threads at the same time, but be careful when it comes to writing. My app had a wonderful bug where it trashed the database when I had database operations in a separate thread until I added proper locking (which you can also enable in the database).

EDIT: Of course, AsyncQueryHandler, I knew there was something like that, but I couldn't remember the name. More elegant than a separate thread, of course.

Btw - how are you storing the database? Is it just a regular database file in your internal storage?