On 9 May 2012, at 7:07am, "Navaneeth.K.N" <navaneet...@gmail.com> wrote:
> I have a SQLite database which has got 60 pages. For performance reasons, I > am thinking of making SQLite cache all of this 60 pages, so for further > queries no disk read will be performed. I believe when all pages are > cached, SQLIte just has to read the cached pages and would be faster. Rather than freak your disk management system, it might be better to use SQLite's own facility for holding databases in memory: <http://www.sqlite.org/inmemorydb.html> So you could copy the data from disk to memory early on, and write changes back to disk when you quit (or once every minute, or something). To copy the data from disk to memory you can either use conventional SELECT and INSERT commands, or use the backup API: <http://www.sqlite.org/backup.html> For 60 pages there may not be that much difference between them. For 600 pages it's definitely worth testing the backup API. > I am not sure how SQLite does the page > caching. So I am iterating over the results using sqlite3_step() and each > iteration will read all of the columns. Is this enough for SQLite to build > the cache? Or just executing the above query without iterating over rows > would be sufficient? Sure. That would cache the data. And then the next thing that needs to be cached might overwrite it all again. You're messing with something that your OS thinks it has sole control over. > Are there any API functions that will tell how many pages are currently > cached, cache misses etc..? The problem with this is it will change from run to run. Sometimes your program will be the only thing running. Other times other apps will be open. Sometimes they'll be printing in the background. Sometimes the OS will be defragging in the background. Sometimes a virus-checker will spring into action. Sometimes the computer will be left idle and a screensaver will start up. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
