1

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.

Improve this question
2
  • Do you have the option of switching to a more robust database? Commented Aug 27, 2011 at 0:50
  • Unfortunately, not in the near term. Commented Aug 27, 2011 at 3:30

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Oracle's new version of BerkleyDB ... is a drop-in replacement for the official SQLite releases. Can a single executable be dropped in?
@Tim, it's s collection of dll's, no executable. SQLite runs in-process with the host app.
1

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...

Improve this answer

5 Comments

So in other words, everything it does, the free versions also do, and and some free versions provide better concurrency through different locking models. Great.
not really sure what you mean... I did neither list all features nor do I think that I should (just a happy customer, nothing else) - I just pointed to features the OP could find useful for what he described... regarding the locking model, AFAIK it supports all models SQLite has to offer...
as I mentioned in my answer, the Oracle BerkleyDB version, which I haven't used, has an alternate locking model which is unique to that version and provides vastly improved concurrency. My issues with your answer are that they list features that are not really related to the question and imply that those features exist only in the commercial version.
I think there are issues in both directions... the OP explicitly stated that he can't change to something else than SQLite (see comments above)...
true, but the BerkleyDB version I mentioned is a port of SQLite; a drop-in replacement. It also fixes the stated problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.