themagician
26th April 2012, 14:23
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:
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("filmquery.db3");
db.open();
5) The tables have proper indexes and all the queries run in 0-2ms when run from e.g. SQLiteSpy
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?
Thanks.
edit: Vacuuming seemed to work.
2) I am using prepared statements
3) I am using transactions
4) Database was default created with:
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("filmquery.db3");
db.open();
5) The tables have proper indexes and all the queries run in 0-2ms when run from e.g. SQLiteSpy
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?
Thanks.
edit: Vacuuming seemed to work.