SQLite query really slow on Android

I have pretty big database that I need to query to get some data and present in a ListView on Android. The db is about 5MB, it's stored on SD card.开发者_如何学JAVA It has 60k records in 2 tables. The problem is that querying the db to get all the records from one specific column takes ridiculously long time - like a few minutes on both emulator and my phone. I've tried everything - storing this data in flat files, xml - sqlite is my last hope. This is the class I'm using to open the database and query it:

    public class DataHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "TableName";
    private Context context;
    private SQLiteDatabase db;
    private SQLiteStatement insertStmt;

    public DataHelper(Context context) {
        this.context = context;
        OpenHelper openHelper = new OpenHelper(this.context);
        // this.db = openHelper.getReadableDatabase();

        this.db = SQLiteDatabase.openDatabase(
                Environment.getExternalStorageDirectory()
                        + "/myDB.db", null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        // this.insertStmt = this.db.compileStatement(INSERT);
    }

    public void deleteAll() {
        this.db.delete(TABLE_NAME, null, null);
    }

    public List<String> selectBrands() {
        List<String> list = new ArrayList<String>();
        Cursor cursor = this.db.query(TABLE_NAME, new String[] { "ColumnName" },
                null, null, null, null, null); 
 }
        if (cursor.moveToFirst()) {
            do {
                if (!(list.contains(cursor.getString(0)))) {
                    list.add(cursor.getString(0));

                }

            } while (cursor.moveToNext());
        }
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
        return list;
    }

    private static class OpenHelper extends SQLiteOpenHelper {
        OpenHelper(Context context) {
            super(context, null, null, DATABASE_VERSION);

        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            onCreate(db);
        }
    }

and putting on the ListView part:

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    try {
        ctxContext = this.getApplicationContext();

        lView = (ListView) findViewById(R.id.ListView01);

        lView.setTextFilterEnabled(true);

        ParseSQLITETask task = new ParseSQLITETask();
        task.execute(null);

    } catch (Exception e) {
        LogErr(e.getMessage());
    }

}

private class ParseSQLITETask extends AsyncTask<Void, Void, Void> {
    @Override
    protected Void doInBackground(Void... urls) {
        try {

            DataHelper dHelper = new DataHelper(getApplicationContext());
            list = (ArrayList<String>) dHelper.selectBrands();

        } catch (Exception e) {
            LogErr(e.getMessage());

        }
        return null;

    }

    @Override
    protected void onProgressUpdate(Void... progress) {

    }

    @Override
    protected void onPostExecute(Void result) {
        try {

            lView.setAdapter(new ArrayAdapter<String>(ctxContext,
                    R.layout.list_item, list));
        } catch (Exception e) {
            LogErr(e.getMessage());

        }
    }

}

You are retrieving the entire record set and converting it into an ArrayList. Don't do this. Pass a Cursor back and use an appropriate Adapter (such as SimpleCursorAdapter).

EDIT: you may also want to consider creating an index on that column, as it may speed up your retrieval time.

Without testing, I would expect your list.contains eats up a lot of the time. You should be able to use SELECT DISTINCT to remove duplicates inside the database library.

EDIT: Femi is correct that you may not need a List of all the records anyway. A Cursor and/or adapter may suffice. Also consider whether you could narrow the results in any way (e.g. a WHERE clause).

Here's the code I ended up using, in case anyone had a similar problem:

public class DataHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "TableName";
    private Context context;
    private SQLiteDatabase db;

    public static final String ColName = "ColumnName";
    public static final String KEY_ID = "_id";

    public DataHelper(Context context) {
        this.context = context;
        OpenHelper openHelper = new OpenHelper(this.context);

        this.db = SQLiteDatabase.openDatabase(
                Environment.getExternalStorageDirectory()
                        + "/myDB.db", null,
                SQLiteDatabase.NO_LOCALIZED_COLLATORS);

    }

    public void deleteAll() {
        this.db.delete(TABLE_NAME, null, null);
    }

    public Cursor selectBrandsCursor() {

        String[] columns = new String[] { ColName, KEY_ID };
        Cursor cursor = this.db.rawQuery("SELECT " + ColName + ", " + KEY_ID
                + " FROM " + TABLE_NAME + " GROUP BY " + ColName + ";", null);

        return cursor;
    }

    private static class OpenHelper extends SQLiteOpenHelper {
        OpenHelper(Context context) {
            super(context, null, null, DATABASE_VERSION);

        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            onCreate(db);
        }
    }
}

Works really well, A LOT faster, duplicate free, thanks to Matthew and Femi for suggestions.

It has nothing directly to do with the topic, but I just found out why a database query is very slow.

Right at the cold start of the app 20.000 database entries were loaded in 100ms. If the same query was repeated in the running app, it took 9000ms (9s).

The reason was the new Android Studio database inspector, which slowed down the query very much.