CODEWITHSUNDEEP
c#sqlitein-memory-database
tofutim
tofutim

Reputation: 23374

Loading on-disk SQLite database into in-memory database and syncing back

I have a SQLite database that has pretty intensive repeated reads and occasional writes. However, the writes (because of indexing) tend to block the reads. I would like to read the on-disk database into a in-memory database and then have a way of syncing back to the on-disk when the machine is completely idle for maybe 5-10 seconds. I was briefly tempted to copy the tables from an attached on-disk database to an in-memory database, but it seems there should be superior way. I also considered transactions which are committed when the machine is idle (but will this block the intensive reads). The reads include the tables to be updated (or inserted), but the writes are not time-sensitive.

Upvotes: 1

Views: 2884

Answers (2)

Yahia
Yahia

Reputation: 70369

IF a commercial library is an option - see http://www.devart.com/dotconnect/sqlite/

It comes (among other things) with support for in-memory-DB and has a component SQLiteDump which basically allows to do what you describe... it comes also with ADO.NET DataSet/DataTable support, LINQ, PLINQ, EF etc. and supports the latest SQListe versions...

Upvotes: 1

Samuel Neff
Samuel Neff

Reputation: 74909

You should upgrade to SQLite 3.7.0 or later which includes Write Ahead Logging. This new method of locking allows reads while writing.

http://www.sqlite.org/draft/wal.html

To copy between an in-memory database and an on-disk database, you can use the backup API but it's not exposed through the .NET wrapper yet.

Also, by increasing your cache-size you can get the same performance from an on-disk database as an in-memory database--the whole thing can be cached in memory.

Another option is using Oracle's new version of BerkleyDB which has a SQLite front end including the same .NET wrapper and is a drop-in replacement for the official SQLite releases. They changed the locking mechanism to support page level locks instead of database level locks and greatly improved concurrency and therefore multi-connection performance. I haven't used it myself, but I read good things.

http://www.oracle.com/technetwork/database/berkeleydb/overview/index.html

Upvotes: 2

Related Questions