Is there any efficient way to make Android database query faster ?

Question

In my android app i need to get 50,000 database entry(text) and compare them with a value when the activity started(in onCreate() ).I am doing this with the simplest way : i get the whole table from db to a cursor.However this way is too laggy.Are there any other way to do it more effective ?

Edit :The app is "scrabble solver" that is why i am not using WHERE close in my query (Take the whole data annd compare it with combination of the input letters).At first i was using a big table which contains whole possible words.Now i am using 26 tables.This reduced the lag and i am making database call on a thread that solved a lot problems too.It is still little bit laggy but much better.

Answer 1

Use a WHERE clause in your SQL rather than reading the whole thing in. Then add an index on the columns in the WHERE clause.

Answer 2

At least you can put index on the field you compare and use WHERE clause. If you are comparing numerics Sqlite (db engine used by Android) supports functions such as MIN and MAX. Also if you are comparing partial strings you can use LIKE. For query optimization there are many resources such as this

Answer 3

You should never read from the database in the UI thread. Use a background thread via AsyncTask or using regular threading. This will fix the UI lag issue your having.

Making the database read faster will help with bringing the data faster to the user but it's even more important that the fetching of the data does not block the user from using the app.

Check out the Following Links:

Painless Threading in Android

YouTube: Writing Zippy Android Apps

Answer 4

To summarize and add a bit more

1. Let the database do the work of searching for you, use a WHERE clause when you perform a query!
2. Google says: "Use question mark parameter markers such as 'phone=?' instead of explicit values in the selection parameter, so that queries that differ only by those values will be recognized as the same for caching purposes."
3. Don't perform any time consuming tasks in onCreate(), always use an AsyncTask or a new Thread
4. If you are careful to only allow one thread to access the SQLiteDatabase at a time you can turn off locking after you open it: db.setLockingEnabled(false);