sqllite read-only optimization

[Jay_Menna]

Sqllight neophyte here.

I have a sqllite database in a Web App. It loads in App.Open

Data is never written to the DB it is strictly read only.

Is there any setting I should set to optimize read only?

[DaveS]

Nope… and FYI… you cannot open an SQLite for ReadOnly,… You can NOT write to it… but there is no ReadOnly “mode”
the only optimizations would be to use VACUUM if another source does a lot of updates (to “defrag” the tables)… make sure appropriate indexes are created…

@348533:@Jay Menna

Data is never written to the DB it is strictly read only.

and of course that is not a truly accurate statement :)… SOMETHING had to write to it at least once

[Tim_Parnell]

I mean if you set the permissions on the SQLite file in such a way, it kind of becomes read-only.

[Bob_Keeney]

Totally depends on the design of the database. If you end up querying on a particular field a lot it’s probably worth indexing that field because it will allow the db to do some optimizations for you.

So the answer is, as with so many other programming things: it depends.

[DaveS]

@348537:@Tim Parnell

I mean if you set the permissions on the SQLite file in such a way, it kind of becomes read-only.

Just DON’T set the permissions ti ReadIonly for the FOLDER the DB is in

[Bob_Keeney]

Another thought is that if this reference data it might be worth loading them into dictionaries in App.Open and then simply getting the data from the dictionaries. Obviously looking things up in a dictionary should be blazingly fast compared to an SQL query.

[scott_boss]

@348540:@Bob Keeney

So the answer is, as with so many other programming things: it depends.

I use that answers a lot with my customers. It is a prefect answer. Not an answer most people want to hear.

[scott_boss]

@348542:@Bob Keeney

Another thought is that if this reference data it might be worth loading them into dictionaries in App.Open and then simply getting the data from the dictionaries. Obviously looking things up in a dictionary should be blazingly fast compared to an SQL query.

or if you want/need the power of SQL, you can load the SQL table(s) into an in memory SQLite database which is not as fast as a Dictionary but pretty darn close. And much faster than reading off of disk.

what the best option will depend on your specifics and we can’t answer that for ya.

[Christian_Schmitz]

I would suggest to set the cache size to match the size of the database so it’s all in memory on the long run.

see
http://monkeybreadsoftware.net/faq-howtosetcachesizeforsqliteorrealsqldatabase.shtml

[Jay_Menna]

@Christian Schmitz Thanks!

Are you saying set the cache to the size of the database itself?

In simple terms. Open the Mac get info -->SQLite - 4.7 MB

db.SQLExecute “PRAGMA cache_size = 4700” ?

[Christian_Schmitz]

You can certainly do that.
For the read only database, the cache will not get bigger than the database, even if you set it bigger.