So you are caching data at the application level that is cached at the database page cache level which is cached in the Operating System file cache that lives in a file residing on disk -- effectively storing three copies of the data in memory.
What advantage does your third-level (application) cache provide that is not provided by the two lower level caches? In other words if the data is already stored in the page cache and (that which isn't) is already in the OS file cache (which it must be since you read the data once already), what benefit does the third-level cache provide other than add the overhead and complication of its management? Likely the most "efficient" way to ensure you do not serve stale data is to get rid of the application level caching and simply re-retrieve the data when you need it. Unless of course you are seriously memory constrained such at running this on a model 5150. Or your query's are very long and complicated. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens >Sent: Tuesday, 7 November, 2017 11:08 >To: SQLite mailing list >Subject: [sqlite] Most efficient way to detect on-disk change > >I'm working with a db that's only written to in transations, and each >transaction increases a db-global version counter. > >This means that I can cache all reads, unless the version changed. > >What would be the most efficient way to make sure I *never* serve >stale >data? > >Right now everything's a single process, so it's really easy, just >clear >the cache on every write. However, I want to be prepared for the near >future where I will have multiple processes using this db file. > >I'm thinking that to detect writes, this might be a safe approach: > >Before serving any cached read, check the timestamp on the wal file. >If it >changed, read the global version. If it changed, clear the cache. >Otherwise, serve the cached read. > >Is it safe to assume that all writes would mean change of the wal >file >timestamp? >More importantly, is it faster to check the timestamp or would a >prepared >query for the version actually be faster (and safer)? > >Also, I'm using WAL right now, but I wonder if that's really useful >given >the single-writer-at-a-time? > >Thank you for your insights, > >Wout. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
