CODEWITHSUNDEEP
androidsqlite
Radek
Radek

Reputation: 1413

android sqlite query optimisation

I have an SQLite db with about 400 000 entries. To query the db I am using the following method:

public double lookUpBigramFrequency(String bigram) throws SQLException {

        SQLiteDatabase db = dbh.getReadableDatabase();
        double frequency = 0;
        bigram = bigram.toLowerCase();

        String select = "SELECT frequency FROM bigrams WHERE bigram = '"
                + bigram + "'";

        Cursor mCursor = db.rawQuery(select, null);
        if (mCursor != null) {

            if (mCursor.moveToFirst()) {
                frequency = Double.parseDouble(mCursor.getString(0));
            } else {
                frequency = 0;
            }
        }

        return frequency;


    }

but it takes about 0.5 sec to retrieve a single entry and having few queries, it builds up and the method is executing for 10 secs. How to speeed it up?

Upvotes: 0

Views: 1650

Answers (2)

njzk2
njzk2

Reputation: 39406

Firstly, use an INDEX

http://www.sqlite.org/lang_createindex.html

in your case that will be something like:

CREATE INDEX idx_bigram ON bigrams (bigram)

Secondly, use '?' instead of literal query. It helps sqlite for caching requests:

String select = "SELECT frequency FROM bigrams WHERE bigram = ?";
Cursor mCursor = db.rawQuery(select, new String[]{ bigram });

Thirdly, I trust query is more efficient than rawQuery:

mCursor = dq.query("bigrams", new String[] { "frequency" }, "bigram = ?",
          new String[]{ bigram }, null, null, null, null);

Fourthly, you can query several values at once (not compatible with point 2):

SELECT frequency FROM bigrams WHERE bigrams IN ('1', '2', '3')

Fifthly, you don't need to open your database every time. You should consider leaving it open.

Edit

After seeing this question IN clause and placeholders it appears you can combine 2 and 4 after all (not sure it is useful, though)

Upvotes: 5

Basavraj
Basavraj

Reputation: 64

Always use transaction mechanism when you want to do lots of database operations

public static void doLotDBOperations() {
    try {
        // Code to Open Database

        // Start transaction
        sqlDb.beginTransaction();

        // Code to Execute all queries

        sqlDb.setTransactionSuccessful();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // End all transaction
        sqlDb.endTransaction();

        // Code to Close Database
    }
}

Upvotes: -1

Related Questions