如何更快地列出 SQLite 中的主键列？

Question

我有一个 .sqlite 数据库，其中包含 4 500 000 个小图像（总共 24 GB）并且有两列。下面是建表代码：CREATE TABLE `OldImages` (`Id`    INTEGER NOT NULL,`Image`    BLOB NOT NULL,CONSTRAINT `PK_Images` PRIMARY KEY(`Id`));我决定将 Id 列表加载到HashSet<long>数据库中以更快地运行时控制数据库中已有的内容，完成 100% 磁盘活动需要 10 个小时。有没有更好的方法来处理这样的事情？除了将列表保存在一个简单的二进制文件中之外——从现在开始我可能最终会这样做，因为它是 36MB 并且可以立即加载。这是 C# 代码：var results = new HashSet<long>();using (var cmd = new SQLiteCommand (Program.DbImages))using ( var transaction = Program.DbImages.BeginTransaction()){    SQLiteDataReader reader;    cmd.CommandText = $"Select Id FROM {table}" ;    reader = cmd.ExecuteReader();    while (reader.Read())    {        var result = reader.GetInt64(0);        results.Add((result));    }    transaction.Commit();}return results;

Answer 1

与其试图弄清楚为什么SQLiteDataReader 这么慢，不如绕过它并使用性能更好的技术。

首先，将Dapper添加到您的项目中。您可以从这里获得它。

然后，添加到类的顶部：

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using Dapper;

我不确定您如何与当前的 SQL 数据提供程序建立连接，但您需要一个连接字符串才能使用 Dapper 进行连接。

var connection = new SqlConnection(myConnectionString);

然后执行以下代码：

var data = connection.Query<long>("SELECT Id FROM {table}");

var hashSet = new HashSet<long>(data);

确保{table}用 SQLite 中的实际表名替换。

Answer 2

好的，在质疑我的代码之前，我应该尝试碎片整理和 HDD 与 SSD。这些测试使用 Dapper（正如 Robert Harvey 的回答中所建议的那样），但这本身并没有速度差异。

150MB 数据库，2 000 000 行，~136 MB RAM：

A 2018 4TB WD Blue HDD = 6m 31s

same HDD after defragmenting the database file = 2m 32s

an old SATA Corsair Force3 120GB SSD = 8s

4 500 000 行，~300 MB RAM：

HDD = 28min

HDD (defrag) = 13min

SSD = 28s (with both Dapper and my original code)

每次测试之前都会重新启动系统以防止缓存。

所以我想一个相当明显的答案是：不要将 HDD（至少不是 4TB WD Blue）用于 SQLite 数据库，如果必须，至少定期对它们进行碎片整理。