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

本文关键字：结果 迭代 性能 问题 数据库 SQLite DataReader 使用 | 更新日期: 2023-09-27 17:50:15

我在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执行此查询时，这工作得很好，我在几秒钟内得到结果(Record表可以包含数十万条记录，查询返回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是惰性加载的(所以它在读取结果之前不会返回整个行集)，我对吗?

EDIT 1:

我不知道这是否与延迟加载有关，就像我最初说的，但我想要的是能够在查询结束后立即获得所有结果。难道不可能吗?在我看来，这真的很奇怪，在几秒钟内执行的查询需要几个小时才能得到结果…

EDIT 2:

我只是在我的选择查询中添加了一个COUNT(*)，以便看看我是否可以在第一个data.Read()中获得结果的总数，只是为了确保它只是结果的迭代，这需要很长时间。我错了:这个新请求在SQLiteAdmin/SQLiteStudio中执行几秒钟，但需要几个小时才能在我的c#项目中执行。你知道为什么同样的查询在我的c#项目中执行的时间长得多吗?

编辑3:

多亏了EXPLAIN QUERY PLAN，我注意到在SQLiteAdmin/SQLiteStudio和我的c#项目之间的相同查询的执行计划略有不同。在第二种情况下，它在DB2Record上使用AUTOMATIC PARTIAL COVERING INDEX，而不是使用主键索引。是否有一种方法可以忽略/禁用使用自动部分覆盖索引?我知道它是用来加速查询的，但在我的情况下，情况正好相反…

谢谢。

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

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

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

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

这可能不会导致任何差异-特别是如果在Value列上没有索引-但值得一试。文本列上的索引需要大量的空间，所以我不会盲目地推荐这样做。

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

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

您确定在System.Data.SQLite, SQLiteStudio和SQLiteAdmin中使用相同版本的sqlite吗?

使用ADO执行SQL查询所需时间不同的另一个典型原因。. 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;")，因为这些选项在某些情况下也可能影响查询性能。

相关文章：