Displaying 100000+ items in ListView from SQLite database

Question

I'm trying to display all the items from a pre-filled database (with over 100000 rows) in a ListView using cursors. It works, but it takes a couple of minutes for the app to start and show the ListView. Is there a faster way? I've read something about FTS3 tables, would that help?

I'm using ArrayList<HashMap<String, String>> with a SimpleAdapter and custom 2-line layout. Code:

Cursor cursor = sDictionary.query("FTSgesla", new String[] {PodatkovnaBaza.KEY_WORD, PodatkovnaBaza.KEY_DEFINITION}, null, null, null, null, PodatkovnaBaza.KEY_WORD);

    if (cursor == null) 
    {

            // There are no results
            mTextView.setText("Empty");
    } 
    else 
    {

        HashMap<String, String> item;

        if(cursor.moveToFirst())
        {
            do
            {
                item = new HashMap<String, String>();
                item.put("line1", cursor.getString(0));
                item.put("line2", cursor.getString(1));
                list.add(item);
            }
            while(cursor.moveToNext());
        }

            sa = new SimpleAdapter(GlavniActivity.this, list,
                    R.layout.result,
                    new String[] { "line1","line2" },
                    new int[] {R.id.word, R.id.definition});

            mListView.setAdapter(sa);
            // Define the on-click listener for the list items
            mListView.setOnItemClickListener(new OnItemClickListener() {

                @Override
                public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                   //do something
                }
            });
        }

score 7 · Accepted Answer · answered Jan 28 '13 at 16:41

7

There are no faster way to load 100000 items, and your user will also not need to see all at once, hence, load them one-by-one with thread.

    private class SQLTask extends AsyncTask<Integer, String[], Integer> {

    @Override
    protected Integer doInBackground(Integer... params) {
        db.open();
        Cursor c = db.getAllDataCursor();
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            String[] temp = new String[c.getColumnCount()];
            for (int i = 0; i < temp.length; i++) {
                temp[i] = c.getString(i);
            }
            publishProgress(thisTask, temp);
        }
        c.close();
        db.close();
    }

    @Override
    protected void onProgressUpdate(String[]... values) {
        super.onProgressUpdate(values);
        // LOAD ONE ROW
    }
}

answered Jan 28 '13 at 16:41

wtsang02

18,603
10
49
67

Is there a SQLite command that would return a cursor with 1000 random values from database? – domen Jan 29 '13 at 14:51
sql doesn't have random but you can use prepared statment and binding to do it. – wtsang02 Jan 29 '13 at 14:58
I'm trying to use your code, but where and how should I load the strings in the ListView? – domen Jan 29 '13 at 18:27
You first create your list and adapter, then add each row in OnProgressUpdate(); this [post](http://stackoverflow.com/questions/4540754/add-dynamically-elements-to-a-listview-android) may help. – wtsang02 Jan 29 '13 at 18:36

score 0 · Answer 2 · answered Jan 28 '13 at 16:41

0

This should be in sections with some kind of filtering, loading that many items just seems why to excessive.

answered Jan 28 '13 at 16:41

draksia

2,371
2
20
25

score 0 · Answer 3 · answered Jan 28 '13 at 16:47

I am not very sure, but I am afraid your problem is you are loading 100.000 views at the same time. Personally I would use ArrayAdapter or base adapter, overriden getView(..) method, and of course using ViewHolder pattern.

Here you got an example very smooth and fast using BaseAdapter: https://github.com/Jachu5/RSS_Feeder_Android/tree/master/RssFeeder_sherlock/src/com/example/adapter

Hope it helps!

score 0 · Answer 4 · answered Jan 28 '13 at 16:53

Why are you ever loading 100K items into a listview? A listview is an onscreen element that will be interacted with by a user. Nobody is ever going to scroll through 100K items. Nobody will even scroll through 1% of that. The answer here is to not load them all.

Displaying 100000+ items in ListView from SQLite database

4 Answers4