SQLite Forum

memory vs mmap
Sorry, I'm a little late to the post, I read it, but by thoughts kept accumulating until final actions.
My assumptions that prevented me from just skipping this topic was
  *  Working and knowning file-mapping for ages, I have an assumption that the mechanism used heavily by modern OSes to save memory space, more or less should work without any penalty. So if a file sector is loaded into memory after the first hardware exception, there should be almost no virtual penalty for the consequence acceses
  *  Observing the sqlite team positive perfectionism, I don't expect any sqlite logical penalty unless it is absolutely necessary.

So I did a test (it's Windows, but I assume some minor hardware/system variations are possible with other desktop/server OSes)

  <code>  create table [testtable] ([id] integer primary key autoincrement, [value]) </code>

  <code>  insert into testtable (value) values(zeroblob(10000))</code> 

The last one executed  10000 times, so we have a 100 MB database

And the test to observe the timing and reading is the cross join 

  <code> select count(*) from testtable, testtable </code>

I chose it because it doesn't consume dynamic memory, heavily resides on reading (since the 100 MB db doesn't fit in sqlite cache), consumes very little in CPU space and because of this is very prone to any delays at VFS reading level. 

My initial tests showed more or less the same difference you mentioned. The memory query took  9 seconds, memory-mapped query took 21 seconds. For both times the cpu and total was almost the same, so more or less no waiting at the I/O level. And by the way, the full-scale file query took 2 minutes and more. Historically I have a "tweak" layer in my admin gui showing the total VFS read (xRead or xFetch) and the average speed of reading. On my machine (Windows 7, not so modern) the mem-mapped version read (fetched) 205 GB and the speed was 9GB/Sec

So something was not right. I looked at what requests came to my tweaked fetch routine, they were mostly equal to page size (4096). So naturally the next test was with the same database with the page increased to  65536. And this time the test performed much better. 10 Seconds, 1 TB total read with 117 GB/Sec fetch speed. So despite the fact that sqlite had to read more pages due to different structure mapping to bigger pages, the new x10 speed fully compensated this. 

So, my version is that for default page size, the penalty is due to granularity of memory mapping view requests related to the page size. And you can probably easily overcome this with a bigger page size. I don't know though whether the improvements are possible for smaller page sizes without sacrificing the clarity of the sqlite VFS/IO level.