At 22:57 30/01/2016, you wrote: >2 further if such a db is part of windows service / application running >continuously then may be pages will remain in the memory under normal >conditions. > >Q1) Operating System like Windows would cache the series of pages, is this >behavior not almost same as that of Cache? > >Q2) Does it make sense to use In-memory SQLite for small size db upto 5MB > average size 2MB?
Howard answered the Windows cache part pretty well: don't rely on it. Now for Q2, I'd use the built-in SQLite backup API to load the DB from disk to memory at launch of the service, use it again to save the DB to disk at reasonable intervals (depends on how the service is sensitive to the D of ACID [durability]) and back again to disk at service shutdown. 2 to 5 Mb is nothing now. Then you don't have to setup a connection SQLite cache of max possible size, in-memory DB is all ou need. Obviously if the machine may be shutdown without notice (e.g. power going off unexpectedly) and the context is sensible to durability of transactions, then all bets are off for in-memory DB.
