CODEWITHSUNDEEP
androiddatabasesqlite
SupaGu
SupaGu

Reputation: 619

Speeding up DB queries on Android

I'm using SQLite on Android using SQLiteDatabase (http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html)

I am developing a bible application, which has a single table with the following columns:

each sentence is broken down in to a series of strongId/word pairs, so wordIdx is used to order the words, strongId is simply a index in to a concordance, and word is the word in the sentence.

so I have 300,000 rows the bottleneck appears to be my query to get a list of words for each verse:

My SQL is effectively this:

SELECT strongId, word FROM ? WHERE book=? AND chapter=? AND verse=?

Here is the code:

Cursor cursor = mBible.database().rawQuery("SELECT " + KEY_STRONGID + "," + KEY_WORD + " FROM " + tableName() + " WHERE " + KEY_BOOK + "=? AND " + KEY_CHAPTER + "=? AND " + KEY_VERSE + "=?" , new String[] { String.valueOf(mChapter.mBook.index()), String.valueOf(mChapter.index()), String.valueOf(verse) });

cursor.moveToFirst();

mWordList = new ArrayList<Word>();
do {
    mWordList.add(new Word(cursor.getString(1), cursor.getString(0)));
} while (cursor.moveToNext());

Now, I've tried putting each chapter in to its own temporary view (using CREATE TEMP VIEW) which cuts down the records to about 400 in my example how ever it is still taking far to long to query

Its taking of the order of 30 seconds to generate the text for two chapters to display to the user (using a temporary view and without using a temporary view). It takes about 5 seconds if I set up a dummy list of words to avoid the database query.

How can I improve the performance of this? It seems as if a temp view is having no impact on performance as I had hoped.

Upvotes: 0

Views: 1215

Answers (2)

SupaGu
SupaGu

Reputation: 619

I ended up creating temporary tables and performance is now acceptable

Upvotes: 0

CL.
CL.

Reputation: 180020

A view does not change the performance of a query; it just saves the query itself, not the results of the query.

If you open your database with the sqlite3 command-line tool on your desktop machine, you can use the EXPLAIN QUERY PLAN command to check how efficient your queries are.

Without any indexes, you query always scans the entire table:

> sqlite3 bible.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> EXPLAIN QUERY PLAN SELECT strongId, word FROM MyTable WHERE book=1 AND chapter=2 AND verse=3;
0|0|0|SCAN TABLE MyTable (~1000 rows)

With an index on your three lookup fields, SQLite can do a fast search in the index and needs to read only the matching records from the table:

sqlite> CREATE INDEX b_c_v ON MyTable(book, chapter, verse);
sqlite> EXPLAIN QUERY PLAN SELECT strongId, word FROM MyTable WHERE book=1 AND chapter=2 AND verse=3;
0|0|0|SEARCH TABLE MyTable USING INDEX b_c_v (book=? AND chapter=? AND verse=?) (~8 rows)

If you create a covering index (with all fields used in the query, lookup fields first), SQLite does not need to read from the table at all. However, this does not give a big speedup over a normal index, and might not be worth the additional storage cost:

sqlite> CREATE INDEX cov ON MyTable(book, chapter, verse, strongId, word);
sqlite> EXPLAIN QUERY PLAN SELECT strongId, word FROM MyTable WHERE book=1 AND chapter=2 AND verse=3;
0|0|0|SEARCH TABLE MyTable USING COVERING INDEX cov (book=? AND chapter=? AND verse=?) (~8 rows)

Please note that SQLite can use at most one index per table in a query, so it does not always make sense to create multiple indexes. Use EXPLAIN QUERY PLAN to check which indexes are actually used, and whether you can create a few indexes to optimize most of your queries.

Also see the Query Planning documentation.

Upvotes: 1

Related Questions