Optimizing fast access to a readonly sqlite database?

Question

I have a huge database and I want my application to work with it as soon as possible. I'm using android so resources are more restricted. I know that its not a good idea to storage huge data in the sqlite database, but I need this.

Each database contain only ONE table and I use it READ only.

What advice can you give me to optimize databases as much as possible. I've already read this post, and except the PRAGMA commands what else can I use?

Maybe there are some special types of the tables which are restricted for read only queries, but principally faster then ordinary table types?

Answer 1

As long as your database fits on the device, there is no problem with that; you'll just have less space for other apps.

There is no special table type. However, if you have queries that use only a subset of a table's columns, and if you have enough space left, consider adding one or more covering indexes.

Being read-only allows the database to be optimized on the desktop, before you deploy it:

• set page size, etc.;
• create useful indexes;
• ANALYZE
• VACUUM

In your app, you might experiment with increasing the page cache size, but if your working set is larger than free memory, that won't help anyway. In any case, random reads from flash are fast, so that would not be much of a problem.

Answer 2

Maybe I'm stating the obvious but you should probably just open it with the SQLITE_OPEN_READONLY flag to sqlite3_open: I think that SQLite will take advantage of this fact and optimize the behaviour of the engine.

Note that all normal SQL(ite) optimization tips still apply (e.g. VACUUMing to finalize the database, setting the correct page size at database creation, proper indexes and so on...)

In addition, if you have multiple threads accessing the database in your application, you may want to try out also the SQLITE_OPEN_NOMUTEX and SQLITE_OPEN_SHAREDCACHE flags (they require sqlite3_open_v2, though)

Answer 3

Huge is relative. But ultimately a device is constrained on storage and memory. So assuming that huge is beyond the typical constraints of a device, you have a few options.

The first option is to store your huge dataset in the cloud and the connected device can offer views into that data by offering cloud services with something like RESTful APIs from the coud to proffer the data to the device. If the device and app rely on always being connected, you don't need as much local storage unless you want to cache data.

Another approach is an occasionally connected device (sometimes offline) where you pull down a slice of the most relevant data to work on to the device. In that model, yo can work offline and push/pull back to the cloud. In this model, sqlite is the storage mechanism to hold that slice of relevant data.

EDIT based on comments:

Concerning optimizing what you have on the device, see the optimization FAQ here:

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

(in rough order of effectiveness)

1. Use an in-memory database
2. Use BEGIN TRANSACTION and END TRANSACTION
3. Use indexes Use PRAGMA cache_size
4. Use PRAGMA synchronous=OFF
5. Compact the database
6. Replace the memory allocation library
7. Use PRAGMA count_changes=OFF

Answer 4

Also you need journalling switch off, because data not change http://www.sqlite.org/pragma.html#pragma_journal_mode PRAGMA journal_mode=OFF