使用C# SQLite DataReader和附加数据库迭代结果的性能问题

Question

我在我的C#项目中使用System.Data.SQLite和SQLiteDataReader。当获取附加数据库查询结果时，我面临着性能问题。

这里有一个搜索两个数据库中文本的查询示例：

ATTACH "db2.db" as db2;

SELECT MainRecord.RecordID,
((LENGTH(MainRecord.Value) - LENGTH(REPLACE(UPPER(MainRecord.Value), UPPER("FirstValueToSearch"), ""))) / 18) AS "FirstResultNumber",
((LENGTH(DB2Record.Value) - LENGTH(REPLACE(UPPER(DB2Record.Value), UPPER("SecondValueToSearch"), ""))) / 19) AS "SecondResultNumber"
FROM main.Record MainRecord
JOIN db2.Record DB2Record ON DB2Record.RecordID BETWEEN (MainRecord.PositionMin) AND (MainRecord.PositionMax)
WHERE FirstResultNumber > 0 AND SecondResultNumber > 0;

DETACH db2;

当使用SQLiteStudio或SQLiteAdmin执行此查询时，这很好，我可以在几秒钟内获得结果（记录表可能包含数十万条记录，查询返回36000条记录）。

当在我的C#项目中执行此查询时，执行也需要几秒钟，但要运行所有结果需要数小时。

以下是我的代码：

// Attach databases

SQLiteDataReader data = null;

using (SQLiteCommand command = this.m_connection.CreateCommand())
{
    command.CommandText = "SELECT...";
    data = command.ExecuteReader();
}

if (data.HasRows)
{
    while (data.Read())
    {
        // Do nothing, just iterate all results
    }
}

data.Close();

// Detach databases

调用SQLiteDataReader的Read方法一次可能需要超过10秒！我猜这是因为SQLiteDataReader是惰性加载的（因此在读取结果之前它不会返回整个行集），我是对的吗？

编辑1：

我不知道这是否与惰性加载有关，就像我最初说的那样，但我想要的是在查询结束时能够立即获取所有结果。这不可能吗？在我看来，这真的很奇怪，一个在几秒钟内执行的查询需要花费数小时才能获得结果...

编辑2：

我只是在我的选择查询中添加了一个COUNT(*)，以便在第一个data.Read()时能够看到是否可以获得总结果数，只是为了确保只是迭代结果需要这么长时间。但我错了：这个新的请求在SQLiteAdmin / SQLiteStudio中在几秒钟内执行，但在我的C#项目中需要几个小时。为什么相同的查询在我的C#项目中执行时间如此之长？

编辑3：

通过使用EXPLAIN QUERY PLAN，我注意到在SQLiteAdmin / SQLiteStudio和我的C#项目中，相同查询的执行计划存在轻微差异。在第二种情况下，它在DB2Record上使用了一个AUTOMATIC PARTIAL COVERING INDEX而不是使用主键索引。有没有办法忽略/禁用自动部分覆盖索引的使用？我知道它用于加速查询，但在我的情况下，相反地发生了...

谢谢。

- Morgan M.

---

你为什么在使用完reader对象之前就释放了command对象？当你说“延迟加载”时，你是从哪里读到的，具体指什么？ - Lasse V. Karlsen

就像我在第一条评论中所说的，我有一个返回SQLiteDataReader对象的ExecuteSingleQuery方法。这就是为什么我在完成读取器之前处理命令。这样做是错误的吗？ - Morgan M.

@SimonMourier：代码没有问题。命令在使用读取器之前被处理，而不是读取器。如果我说错了，请告诉我。但它可以工作。 - Morgan M.

一般来说，考虑使用 using 而不是手动调用 Close()，以确保即使发生异常也会调用 Close()。 - Eldritch Conundrum

是的，通常我会在包围代码的try/catch的finally子句中调用Close...但我同意你的观点，使用using仍然更好。 - Morgan M.

Answer 1

您确定在 System.Data.SQLite、SQLiteStudio 和 SQLiteAdmin 中使用的是相同版本的 sqlite 吗？它们之间可能存在巨大差异。

- tafia

---

我相信SQLiteAdmin和SQLiteStudio直接使用sqlite3.dll，而我的C#项目使用System.Data.SQLite（依赖于sqlite3.dll）。 - Morgan M.

我是指哪个版本的sqlite3.dll。查询规划器会给出非常不同的结果。当我进行这样的比较时，我被卡住了很长时间...直到我决定使用与我的应用程序相同的dll构建自己的GUI。尝试在其中三个上运行select sqlite_version();。 - tafia

你是正确的，他们使用了不同版本的sqlite3.dll。 我的C#项目中使用的版本（3.8）比SQLiteAdmin和SQLiteStudio中使用的版本（3.7）要新。 - Morgan M.

你能否尝试添加一个对应于自动生成的索引的索引？...并/或者尝试在两个数据库上运行analyze（更多信息请参见此处） - tafia

我不知道analyze命令。我一定会尝试一下。谢谢。 - Morgan M.

根据我的经验，它有时很有帮助，但并不总是有效。无论如何，在执行“分析”命令之前，您都可以删除sqlite_stat表来恢复之前的状态。 - tafia

Answer 2

除了找到匹配的记录之外，似乎您还在计算字符串匹配的次数。此计数结果也用于 WHERE 子句中。

您需要的是匹配次数，但在 WHERE 子句中匹配次数并不重要 - 您可以尝试更改 WHERE 子句为：

WHERE MainRecord.Value LIKE '%FirstValueToSearch%' AND DB2Record.Value LIKE '%SecondValueToSearch%'

尽管如此，这可能不会产生任何区别 - 尤其是如果在“Value”列上没有索引 - 但值得一试。文本列上的索引需要很多空间，所以我不会盲目推荐。

如果您还没有这样做，请在DB2的“RecordID”列上放置一个索引。

您可以使用“EXPLAIN QUERY PLAN SELECT ...”来使SQLite输出它所做的内容，以尝试使您的查询执行，该输出可能有助于诊断问题。

- C.Evenhuis

---

是的，在WHERE子句中没有使用匹配数，但我的意图是在查询执行后在代码中使用它。无论如何，我仍然尝试了您的建议，确实更快，但并没有解决问题。不，我没有在DB2的RecordID列上放置索引，因为它是主键，我相信SQLite会自动为主键创建索引，对吗？我将尝试EXPLAIN QUERY PLAN SELECT。谢谢 :) - Morgan M.

@MorganM。是的，主键是一个索引。 - C.Evenhuis

由于“EXPLAIN QUERY PLAN”的帮助，我注意到在SQLiteAdmin / SQLiteStudio和我的C#项目之间相同查询的执行计划存在轻微差异。在第二种情况下，它使用了DB2Record上的自动部分覆盖索引而不是使用主键索引。有没有一种方法可以忽略/禁用自动部分覆盖索引的使用？我知道它被用来加速查询，但在我的情况下，恰恰相反... - Morgan M.

@MorganM。恐怕我不知道如何指示SQLite更改其计划，除了完全删除索引以外。据我所知，“部分覆盖”意味着它无法从索引中获取所有请求的数据，必须在DB2Record中查找实际记录。 - C.Evenhuis

根据这篇文章 https://dev59.com/2GIj5IYBdhLWcg3wMifW 中所述，“Automatic”指的是SQLite创建一个临时索引，该索引仅用于此查询，并在之后被删除。 因此，我担心我不能删除它。 - Morgan M.

Answer 3

SQL查询在ADO.NET和原始实用工具（如SQLiteAdmin）中执行时可能需要不同的时间，这是一个典型的原因是CommandText中使用了命令参数（从您的代码中无法确定是否使用了参数）。根据ADO.NET提供程序实现，以下相同的CommandText值：

SELECT * FROM sometable WHERE somefield = ?   // assume parameter is '2'

并且

SELECT * FROM sometable WHERE somefield='2'

可能会导致完全不同的执行计划和查询性能。

另一个建议：您可以禁用日志记录（在连接字符串中指定“Journal Mode=off;”）和同步模式（“Synchronous=off;”），因为这些选项在某些情况下也可能影响查询性能。

- Vitaliy Fedorchenko

---

谢谢您的回答，但我不再使用参数了。它们确实减慢了查询的执行速度（特别是当我试图最大化每秒插入性能时）。 - Morgan M.

稍微离题一下，关于插入性能：我对使用sqlite3.exe实用程序（.import 命令）从C#代码执行某种“批量加载”来插入大量记录有很好的经验——它允许比单个INSERT命令更快地插入记录。 - Vitaliy Fedorchenko