Joe Wilson wrote:
I think some other factor is at play here.
SQLite 2.x's memory databases are still twice as fast at batch inserts
than either 3.x's disk-based databases or 2.x's disk-based databases
when the DB size is less than physical machine memory.
Joe,
Yes there is another factor at work here. In version 3.x SQLite uses the
same pager for both memory and file based databases. The file based
pager calls to the OS for I/O to the disk file, but SQLite implements
its own I/O (really read/write) routines to get and save pages in a
memory based database. In version 2.x the memory based databases used a
completely different pager than the file based databases. This change
was made to simplify the code and helps to ensure all features work the
same regardless of where the database pages are stored, but it has
definitely slowed down the memory based databases. Memory based database
used to be twice as fast as file based databases, but they are now
slightly slower than file based databases. I suspect there are
optimizations that could be made to the memory I/O routines to speed
them up, they should at least be able to run slightly faster than file
based I/O.
Dennis Cote
