1) The database file is 455MB in size with 6 large tables with only a few columns in each, but millions of rows
2) I am using prepared statements
3) I am using transactions
4) Database was default created with:
5) The tables have proper indexes and all the queries run in 0-2ms when run from e.g. SQLiteSpy
To copy to clipboard, switch view to plain text mode
My app needs to do a lot of DB queries and when I first run the database querying function it takes a very long time to execute the queries and you can hear the hard drive reading a lot, but when I run it again then it's very fast, like it caches the whole database. Could I e.g. load the database in memory when I load the app? What optimization tricks are there to make it faster?
edit: Vacuuming seemed to work.