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

Question

In my Android app, I need to get 50,000 database entries (text) and compare them with a value when the activity starts (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 ways to do it more effectively ?

Edit: The app is "scrabble solver" that is why I am not using WHERE clause in my query (Take the whole data and 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 calls on a thread - that solved a lot problems too. It is still little bit laggy but much better.

Answer 1

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. If your where clause does not operate on the primary key column or a unique column you should create an index for that column. Here is some info on how to do that: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#indexes
3. 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."
4. Run the query analysis using EXPLAIN QUERY PLAN http://www.sqlite.org/lang_explain.html and look for any scan operations, these are much slower than search operations. Uses indexes to avoid scan operations.
5. Don't perform any time consuming tasks in onCreate(), always use an AsyncTask, a Handler running on a background thread or some other non-main thread.
6. If you need to do full text search please read: http://www.sqlite.org/fts3.html

Answer 2

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 3

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 4

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