android sqlite query optimisation

Question

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?

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

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)

edited May 23 '17 at 12:19

Community

1
1

answered Oct 11 '11 at 10:37

njzk2

38,969
7
69
107

#1 and #5 are the most significant in terms of performance; #2 is important to avoid SQL injections, and simply looks better – Javier Oct 11 '11 at 10:41
+1 for the non-literal querys. -1 for assuming that `query()` would be faster then `rawQuery()`. – Lukas Knuth Oct 11 '11 at 10:41
it improved the speed by about 10-20 ms but that's still not enough – Radek Oct 11 '11 at 11:16
Well actually I messed up the indexes at first. Did it again and it helped. Thanks – Radek Oct 11 '11 at 12:29
Yes, using selection args works a bit faster. every millisecond count – Erich García Jun 16 '18 at 01:56

score -1 · Answer 2 · answered Oct 11 '11 at 10:38

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
    }
}

which would be relevant for write operations. – njzk2 Oct 11 '11 at 10:43 — njzk2, Oct 11 '11 at 10:43

android sqlite query optimisation

2 Answers2

Edit