如何更快地列出 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 31ssame HDD after defragmenting the database file = 2m 32san old SATA Corsair Force3 120GB SSD = 8s4 500 000 行，~300 MB RAM：HDD = 28minHDD (defrag) = 13minSSD = 28s (with both Dapper and my original code)每次测试之前都会重新启动系统以防止缓存。所以我想一个相当明显的答案是：不要将 HDD（至少不是 4TB WD Blue）用于 SQLite 数据库，如果必须，至少定期对它们进行碎片整理。