将磁盘上的 SQLite 数据库加载到内存数据库中并同步回来
我有一个 SQLite 数据库，它有相当密集的重复读取和偶尔的写入。然而，写入（由于索引）往往会阻塞读取。我想将磁盘数据库读入内存数据库，然后当机器完全空闲大约 5-10 秒时同步回磁盘数据库。我一度想将表从附加的磁盘数据库复制到内存数据库，但似乎应该有更好的方法。我还考虑了在机器空闲时提交的事务（但这会阻止密集读取）。读取包括要更新（或插入）的表，但写入对时间不敏感。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该升级到 SQLite 3.7.0 或更高版本，其中包括预写日志记录。这种新的锁定方法允许在写入时读取。
http://www.sqlite.org/draft/wal.html
在内存数据库和磁盘数据库，您可以使用备份 API，但它尚未通过 .NET 包装器公开。
此外，通过增加缓存大小，您可以从磁盘数据库获得与内存数据库相同的性能——整个数据库都可以缓存在内存中。
另一种选择是使用 Oracle 的新版本 BerkleyDB，它有一个 SQLite 前端，包括相同的 .NET 包装器，并且是官方 SQLite 版本的直接替代品。他们更改了锁定机制以支持页面级锁而不是数据库级锁，并大大提高了并发性，从而提高了多连接性能。我自己没用过，但我读过好东西。
http://www.oracle.com/technetwork/database/berkeleydb/overview /index.html
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
如果可以选择商业库 - 请参阅 http://www.devart.com/dotconnect/sqlite/< /a>
它（除其他外）支持内存数据库，并具有一个组件 SQLiteDump，它基本上允许执行您所描述的操作...它还附带 ADO.NET
DataSet/DataTable支持， LINQ、PLINQ、EF 等并支持最新的 SQListe 版本...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/DataTablesupport, LINQ, PLINQ, EF etc. and supports the latest SQListe versions...