c# - 使用 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 而不是使用主键索引。有没有办法忽略/禁用自动部分覆盖索引的使用？我知道它用于加快查询速度，但就我而言，情况恰恰相反......

谢谢。

Answer 1

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

您想要匹配的数量，但匹配的数量在 WHERE 子句中并不重要 - 您可以尝试将 WHERE 子句更改为:

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

虽然它可能不会产生任何差异 - 特别是如果 Value 列上没有索引 - 但值得一试。文本列上的索引需要大量空间，所以我不会盲目推荐。

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

您可以使用 EXPLAIN QUERY PLAN SELECT ... 让 SQLite 吐出它所做的尝试使您的查询执行，其输出可能有助于诊断问题。