将sqlite数据库从内存同步到文件 [英] Synchronizing sqlite database from memory to file

问题描述

我在写一个应用程序，它必须非常频繁地记录信息，例如，一秒钟两次。我希望将信息保存到sqlite数据库，但我不介意每十分钟向磁盘提交一次更改。

使用文件时执行查询 - 数据库需要很长，并使计算机滞后。

一个可选的解决方案是使用内存数据库（它将适合，没有后顾之忧）到磁盘，

有可能吗？有没有更好的方法来实现（你能告诉sqlite提交到磁盘只有在 X 查询？）。

<我可以用 Qt 的 SQL wrapper来解决这个问题吗？

解决方案

让我们假设你有一个名为disk_logs的磁盘数据库，名为events。您可以将内存数据库附加到现有数据库：

  ATTACH DATABASE'：memory：'AS mem_logs; 
 

在该数据库中创建一个表（完全在内存中）以接收传入日志事件：

  CREATE TABLE mem_logs.events（a，b，c）; 
  

然后在应用程序停机期间将数据从内存表传输到磁盘表： / p>



  INSERT INTO disk_logs.events SELECT * FROM mem_logs.events; 
  

然后删除现有内存表的内容。重复。

这是非常复杂的，但...如果你的记录跨越多个表，并与外键链接在一起，这可能是一个痛苦，保持这些同步

在尝试这样的操作（不适当的过度设计）之前， d还建议尝试使SQLite尽可能快地 。 SQLite应该能够轻松地手动>每秒50K记录插入。每秒两次的几个日志条目不应导致显着的减速。

I'm writing an application which must log information pretty frequently, say, twice in a second. I wish to save the information to an sqlite database, however I don't mind to commit changes to the disk once every ten minutes.

Executing my queries when using a file-database takes to long, and makes the computer lag.

An optional solution is to use an in-memory database (it will fit, no worries), and synchronize it to the disk from time to time,

Is it possible? Is there a better way to achieve that (can you tell sqlite to commit to disk only after X queries?).

Can I solve this with Qt's SQL wrapper?

解决方案

Let's assume you have an on-disk database called 'disk_logs' with a table called 'events'. You could attach an in-memory database to your existing database:

ATTACH DATABASE ':memory:' AS mem_logs;

Create a table in that database (which would be entirely in-memory) to receive the incoming log events:

CREATE TABLE mem_logs.events(a, b, c);

Then transfer the data from the in-memory table to the on-disk table during application downtime:

INSERT INTO disk_logs.events SELECT * FROM mem_logs.events;

And then delete the contents of the existing in-memory table. Repeat.

This is pretty complicated though... If your records span multiple tables and are linked together with foreign keys, it might be a pain to keep these in sync as you copy from an in-memory tables to on-disk tables.

Before attempting something (uncomfortably over-engineered) like this, I'd also suggest trying to make SQLite go as fast as possible. SQLite should be able to easily handly > 50K record inserts per second. A few log entries twice a second should not cause significant slowdown.

这篇关于将sqlite数据库从内存同步到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！