Optimisation techniques for sqlite database

Question

I'm creating Android application that contains a large amount of data. It takes lot of time to access the data. What are optimization technique that I can use ? Are there any optimized queries?

Answer 1

This page will give you a lot of good tips on how to optimize SQLite things:

http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

Keep in mind that some stuff SQLite will optimize for you as well when it runs a query:

http://www.sqlite.org/optoverview.html

Answer 2

Use parameterized queries to reduce the amount of queries that needs to be parsed, see: http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251

Answer 3

Hi

SQLite does not support dates natively (SQLite uses strings to store dates). If you are using an index to access dates - you'll get slow query times (not to mention inaccurate or wrong results..)

If you really need to sort through dates then I'd suggest you create separate columns for the various date elements that you want to index (like years, months, and days). Define these columns as integers and add INDEX statements to index their contents.

Good Luck

Liron Levi

Creator of the SQLite Compare diff/merge utility (See a demo video at YouTube)

Answer 4

You can use parametrized statements in Android:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement%28java.lang.String%29