Sqlite Optimization: Read only scenario

Question

I use SQLite for a number of application on the desktop and PDA. Most operations are readonly, as SQLite functions as a data store for reference material in my applications.

Basically, I am looking for suggestions on improving performance in a scenario where you know the access to the data is only read-only.

Maybe via various pragma settings? etc...

SQLite performance is excellent, however on the PDA when you have multiple databases I can see a small performance hit. I dont think this is a problem with SQLite, just the reality of the speed of a PDA. However, having said that, maybe there are ways to improve it.

Answer 1

Have you tried de-normalising the data?

Answer 2

The standard database performance tips still apply:

• Make sure your queries use indexes rather than full table scans
• Be as selective as you can in your queries so you aren't pulling unneeded rows from the db
• Select only the columns you want

Answer 3

Good advice and well put. I am hoping for something more specific in telling the engine about what I am doing. For example, telling the engine there will be no multiple writes to the DB, or modifying the cache handling in some way.

However, I am glad you called attention to the "design" aspect of the database as a leading issue.

Answer 4

You can call sqlite3_open_v2() with the flag SQLITE_OPEN_READONLY. I have no idea if sqlite3 actually uses that to optimize its behavior, or just as way to set the appropriate permissions on the open call it makes to the OS.

Answer 5

sqlite3_open_v2() with the flag SQLITE_OPEN_READONLY alters the way SQLite handles opportunistic locks no real performance advantages. You could use a pragma cache_size if you are doing lots of reads or depending on the size of the db make an in memory copy of the db using the :memory open option.