SQLite Forum

memory vs mmap
I don't know enough about the implementation detail, but I was trying to show you a big reason why an identical code path in SQLite accessing "simple raw pointers" could have wildly varying execution speeds due to the decisions made in implementing that underlying mapping object.

It is my experience that an mmap using application will typically create one big mapping object for an entire file, then any access to that will access the underlying memory object. However, I believe that due to the design decisions when mmap was first added to SQLite, it is only used for reading (not writing) data (which is your use case) as long as the file was under some certain size. Subsequently, I seem to recall that problems were discovered with one or more platforms mmap implementations and it was "deprecated" (that might not be the right word, but it seems right).

The page cache may perform one big allocation then access individual pages within that, but there is no requirement that the pages be continuous. A mmap object will necessarily be a contiguous chunk of the file.

So effectively you have one big mmap object that represents a sequential range of pages of the file, or you have a page cache which represents many potentially random pages in many page size chunks. This means that the method of accessing a memory DB will be of necessity very different than the code that accesses a mmap version, even though they both use pointers.