Fastest way to search through strings stored in sqlite database

Question

I have large number of strings, approximately 15,000 that I stored in a SQLite database using the following code:

 void addKey(String key, String value, String table) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_KEY, key); // Contact Name
        values.put(KEY_VALUE, value); // Contact Phone

        // Inserting Row
        db.insert(table, null, values);
        db.close(); // Closing database connection

    }

And then i search through that database using the following method in order to pick out any strings that match the key im looking for:

public String searchKeyString(String key, String table){
        String rtn = "";
        Log.d("searchKeyString",table);

            // Select All Query
            String selectQuery = "SELECT  * FROM " + table;

            SQLiteDatabase db = this.getWritableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    Log.d("searchKeyString","searching");

                    if(cursor.getString(1).equals(key)) 
                        rtn = rtn + "," + cursor.getString(2);
                } while (cursor.moveToNext());
            }
            cursor.close();
            db.close();
            Log.d("searchKeyString","finish search");

        return rtn;
    }

The goal is to do this in real time as the user is typing on the keep board so response time is key and the way it stands now it takes over a second to run through the search.

I considered reading all of the items into an array list initially and sorting through that which might be faster, but i thought an array list of that size might cause memory issues. What is the best way to search through these entries in my database?

Answer 1

A couple of things you can do...

• Change the return to a StringBuilder until the end.
• Only use a readable version of the database (that's probably not making much difference though)
• Do not get a new instance of the database every time, keep it opened until you don't need it anymore
• Query for only what you need with the "WHERE" argument in the SQL query.

See the code below with some changes:

// move this somewhere else in your Activity or such
SQLiteDatabase db = this.getReadableDatabase();

public String searchKeyString(String key, String table){
    StringBuilder rtn = new StringBuilder();
    Log.d("searchKeyString",table);

        // Select All Query
        String selectQuery = "SELECT  * FROM " + table + " WHERE KEY_KEY=?";

        Cursor cursor = db.rawQuery(selectQuery,  new String[] {key});
        // you can change it to
        // db.rawQuery("SELECT * FROM "+table+" WHERE KEY_KEY LIKE ?", new String[] {key+"%"});
        // if you want to get everything starting with that key value

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Log.d("searchKeyString","searching");

                rtn.append(",").append(cursor.getString(2));
            } while (cursor.moveToNext());
        }
        cursor.close();
        Log.d("searchKeyString","finish search");

    return rtn.toString();
}

Note even if you want this to happen in "real-time" for the user, you will still need to move this to a separate Thread or ASyncTask or you are going to run into problems....