[odb-users] Closing SQLite Database/Performance Gains

[Boris Kolpackov]

Hi Eric,

Boris Kolpackov <boris at codesynthesis.com> writes:

> I had to modify the test to run single-threaded (an in-memory SQLite 
> database cannot be shared among multiple threads, unlike an on-disk 
> one).

I did some more digging into this and it appears that starting with
SQLite 3.7.13 it is possible to share an in-memory database among
multiple threads (i.e., using the shared cache SQLite mechanism).

However, to achieve an in-memory database sharing, one has to
perform a few special steps. Firstly, for backwards compatibility,
using the ':memory:' special name will never result in the shared
database. Instead, we have to use the new URI syntax. Secondly, we
need to enable the URI support with the SQLITE_OPEN_URI flag. Here
is a sample sqlite::database constructor call that opens a shared
in-memory database:

odb::sqlite::database db (
  "file:/memory1?mode=memory",
  SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI);

The '/memory1' name in the URI above identifies the database in
the process (it is not a real file name).

I also re-ran the threads test in its original version (32 threads)
both for an in-memory and on-disk databases. Here are the results:

Database on a SAS 15K rpm disk:   54sec, 95% CPU utilization
Database in memory:               45sec, 110% CPU utilization

Boris