System.Data.Sqlite：缓存大小=...连接字符串参数似乎不起作用
我正在比较磁盘和内存数据库的速度。我正在尝试使用以下连接字符串将大约 8mb 的数据插入到 sqlite3 数据库中：
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=db.db3;Version=3;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
它会在大约 4 秒内插入我的数据。
当我将连接字符串更改为以下字符串时：
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=:memory:;Version=3;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
我在大约 0.5 秒内插入了数据。
但是，当我按照以下方式更改设置时（注意Cache Size=16777216），
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=db.db3;Version=3;Cache Size=16777216;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
结果又是 4 秒，并且结果与缓存量无关。根据我的理解，缓存大小是数据量的两倍应该会使我的数据在内存中稳定下来。当没有更多缓存剩余时，应该发生磁盘迁移...
如何使 sqlite 响应更快？
I'm comparing speeds of disk and in-memory databases. I'm trying to insert approximately 8mb of data to a sqlite3 db with the following connection string:
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=db.db3;Version=3;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
It gets my data inserted in around 4 seconds.
When I change my connection string to the following one:
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=:memory:;Version=3;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
I get my data inserted in around 0.5 seconds.
But when I change the settings the following way (note Cache Size=16777216)
<configuration>
<connectionStrings>
<add name="Server" connectionString="Data Source=db.db3;Version=3;Cache Size=16777216;" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
This results 4 seconds again, and result is independent of the amount of cache. In my understanding having the cache size twice as large as the amount of data should make my data settle in memory. And disk migrations should occur when no more cache left...
How can I make sqlite more responsive?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它比较慢，因为：内存意味着数据库在内存中，磁盘总是会比较慢。您可以通过将插入包装在事务中来加快速度，因为它只会在事务结束时刷新到磁盘一次。翻转交易日志也会有所帮助，但会使其更容易因崩溃和断电而出现问题。
缓存不太可能加快任何速度，因为您不会多次使用同一数据页。
It's slower because :memory means the database in memory, disk will always be slower. You may be able to speed things up a bit by wrapping the inserts in a transaction as it will only flush to disk once at the end of the transaction. Turning of the transaction journal will help as well but will make it more prone to issues due to crashes and power loss.
It's unlikely the cache will speed anything up because you aren't using the same page of data more than once.
根据 SQL Lite 文档，缓存大小的设置仅是建议。 “是否遵守此建议由应用程序定义的页面缓存自行决定”
According to the SQL Lite Documentation the setting of cache size is a suggestion only. "Whether or not this suggestion is honored is at the discretion of the Application Defined Page Cache"