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

问题描述

我在我的 C# 项目中使用 System.Data.SQLite 和 SQLiteDataReader.在获取带有附加数据库的查询结果时，我遇到了性能问题.

I am using System.Data.SQLite and SQLiteDataReader in my C# project. I am facing performance issues when getting the results of a query with attached databases.

以下是在两个数据库中搜索文本的查询示例:

Here is an example of a query to search text into two databases :

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 条记录).

When executing this query with SQLiteStudio or SQLiteAdmin, this works fine, I am getting the results in a few seconds (the Record table can contain hundreds of thousands of records, the query returns 36000 records).

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

When executing this query in my C# project, the execution takes a few seconds too, but it takes hours to run through all the results.

这是我的代码:

// 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 是延迟加载的(因此它在读取结果之前不会返回整个行集)，对吗?

Calling the Read method of the SQLiteDataReader once can take more than 10 seconds ! I guess this is because the SQLiteDataReader is lazy loaded (and so it doesn't return the whole rowset before reading the results), am I right ?

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

I don't know if this has something to do with lazy loading, like I said initially, but all I want is being able to get ALL the results as soon as the query is ended. Isn't it possible ? In my opinion, this is really strange that it takes hours to get results of a query executed in few seconds...

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

I just added a COUNT(*) in my select query in order to see if I could get the total number of results at the first data.Read(), just to be sure that it was only the iteration of the results that was taking so long. And I was wrong : this new request executes in few seconds in SQLiteAdmin / SQLiteStudio, but takes hours to execute in my C# project. Any idea why the same query is so much longer to execute in my C# project?

感谢 EXPLAIN QUERY PLAN，我注意到 SQLiteAdmin/SQLiteStudio 和我的 C# 项目之间相同查询的执行计划略有不同.在第二种情况下，它在 DB2Record 上使用 AUTOMATIC PARTIAL COVERING INDEX 而不是使用主键索引.有没有办法忽略/禁用自动部分覆盖索引的使用?我知道它用于加快查询速度，但就我而言，情况恰恰相反......

Thanks to EXPLAIN QUERY PLAN, I noticed that there was a slight difference in the execution plan for the same query between SQLiteAdmin / SQLiteStudio and my C# project. In the second case, it is using an AUTOMATIC PARTIAL COVERING INDEX on DB2Record instead of using the primary key index. Is there a way to ignore / disable the use of automatic partial covering indexes? I know it is used to speed up the queries, but in my case, it's rather the opposite that happens...

谢谢.

推荐答案

您确定在 System.Data.SQLite、SQLiteStudio 和 SQLiteAdmin 中使用相同版本的 sqlite 吗?你可以有很大的不同.

Are you sure you use the same version of sqlite in System.Data.SQLite, SQLiteStudio and SQLiteAdmin ?You can have huge differences.

这篇关于使用 C# SQLite DataReader 和附加数据库迭代结果的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！