SQLite is really slow on Android

Question

I have a fairly large database that I need to query to get some data and be present in the ListView on Android. The DB is about 5 MB, it is stored on the SD card. It has 60 thousand records in 2 tables. The problem is that it takes a ridiculously long time to query a db to retrieve all the records from one particular column - for example, several minutes both on the emulator and on my phone. I tried everything - storing this data in flat files, xml - sqlite - my last hope. This is the class that I use to open the database and query:

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 adding 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()); } } } 

Answer 1

You retrieve the entire set of records and convert it to an ArrayList . Do not do this. Skip the cursor back and use the appropriate adapter (for example, SimpleCursorAdapter ).

EDIT: You can also consider creating an index in this column, as this can speed up your search time.

Answer 2

Without testing, I would expect your list.contains eat up a lot of 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 all entries. You may need a cursor and / or adapter. Also consider whether you can narrow the results in some way (for example, the WHERE clause).

Answer 3

Here is the code I used in case someone 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 well, MUCH faster, duplicates for free, thanks to Matthew and Femi for the suggestions.