Mark Spiegel wrote: > [...]
Thanks for the explanation. Yes, I'll have to make sure not to use this technique when the file is too large. But I think there is definitively an improvement for us to pull data in cache whenever possible. > Given that you can't write your own VFS, there is not much to suggest, > but one question to ask. Is the sum of the time for the pre-read you > perform and the subsequent database operation(s) smaller than doing the > database operation(s) without the pre-read? I see that in the 3.5.x > source Dr. Hipp gives the file system the proper random access hint to > the file system when opening databases. This is just a hint to the > cache manager and it is not obligated to honor it, but it will > effectively shut down most read ahead and large block reads which is > what you are getting when you sequentially pre-read. I can give you a few timings I have to give you an idea: Initialisation: connects to DB, pro-compile some queries, load structured data (SELECT * FROM Objects; i.e. sequentially, no blobs) Load Blobs: load 1/4 of all Blobs (each ~23k of size, I just checked), "randomly" -Uncached -14s to initialise -31s to load blobs -Uncached, but VACUUMed -3s to initialise -16s to load blobs -Pre cached, no VACUUM -3s to cache and initialise -1s to load blobs Notes: - VACUUM took (as expected) a very long time to do it's task (few minutes). - This test is for a ~100MB file. From other tests I made it looks like the timings depend linearly on size of file, and number of blobs loaded Pre-caching is clearly a winner here. > One more thing, did raising the limit on the number of pages SQLITE can > cache internally have any effect? I just tried (hadn't noticed that option before) to go from 2000 to 4000 and 8000, without noticing any difference. I might try next week to raise the page size to 50k and see if it makes a difference?
