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?

Community · Accepted Answer · 2017-05-23 12:19:56Z

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, 2017 at 12:19

CommunityBot

11 silver badge

answered Oct 11, 2011 at 10:37

njzk2

39.4k7 gold badges72 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Javier Over a year ago

#1 and #5 are the most significant in terms of performance; #2 is important to avoid SQL injections, and simply looks better

Lukas Knuth Over a year ago

+1 for the non-literal querys. -1 for assuming that query() would be faster then rawQuery().

Radek Over a year ago

it improved the speed by about 10-20 ms but that's still not enough

Radek Over a year ago

Well actually I messed up the indexes at first. Did it again and it helped. Thanks

Erich García Over a year ago

Yes, using selection args works a bit faster. every millisecond count

Basavraj · Accepted Answer · 2011-10-11 10:38:41Z

-1

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

answered Oct 11, 2011 at 10:38

Basavraj

642 bronze badges

1 Comment

njzk2 Over a year ago

which would be relevant for write operations.

Collectives™ on Stack Overflow

android sqlite query optimisation

2 Answers 2

Edit

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related