使用较新版本的 System.Data.SQLite/sqlite3.dll 在 SQLite 数据库上使用子查询进行查询大约慢 10 倍 [英] Querying using subqueries on a SQLite database approx 10x slower using newer versions of System.Data.SQLite/sqlite3.dll

问题描述

(见下方更新)

在从 C#.Net 应用程序(约 5 秒)中查询大约 500,000 行的非常简单的 Sqlite 数据表时，我遇到了查询性能缓慢的问题.

I am having an issue of slow query performance when querying a very simplistic Sqlite datatable of about 500,000 rows from within a C#.Net application (~5sec).

我使用 LinqPad 以及 2 个数据库浏览器(均基于 QtSql)在完全相同的数据库上尝试了完全相同的查询，它的运行速度提高了 10 倍(约 0.5 秒).相同的查询，相同的数据库，不同的应用程序，只有我的运行速度不快.

I have tried the exact same query on exactly the same database using LinqPad, as well as 2 database browsers (both based on QtSql), and it runs 10x faster (~0.5secs). Same query, same db, different apps, only mine doesn't run fast.

无论我是返回值还是仅返回一个 Count(*)，它的区别可以忽略不计.

It makes negligible difference whether I'm returning values or just a Count(*).

我试过了:

• 为每个 .Net 3.5/4/4.5 构建
• 为 AnyCPU/x86/x64 中的每一个构建
• 使用 System.Data.Sqlite、sqlite-net 中的每一个，以及通过 COM 直接访问 sqlite3 dll
• 为每个 WPF/WinForms 构建
• 查询的不同变体

这些都不会对查询时间产生任何明显的影响.

None of these make any noticible difference to the query time.

我知道使用 JOIN 重写查询可能会有所帮助，但我无法弄清楚为什么相同的查询在 LinqPad/Sql 浏览器中可以正常工作，但在我尝试创建的任何应用程序中都不能正常工作.我一定遗漏了一些非常基本的东西.

I know that rewriting the query using JOINs may help, but what I can't figure out is why the same query works fine in LinqPad/Sql browers but not from any app I try to create. I must be missing something pretty fundamental.

示例表:

"CREATE TABLE items(id INTEGER PRIMARY KEY, id1 INTEGER, id2 INTEGER, value INTEGER)"

示例查询字符串(尽管基本上任何使用子查询的查询都需要很长时间):

Example Query String (though basically any query using a subquery takes a long time):

SELECT count(*) 
FROM items WHERE 
id2 IN 
(
    SELECT DISTINCT id2 FROM items WHERE id1 IN 
    (
        SELECT DISTINCT id1 FROM items WHERE id2 = 100000 AND value = 10
    )
    AND value = 10
) 
AND value = 10 
GROUP BY id2

我知道这可能会使用 JOINS 和索引来重新编写以加快速度，但事实仍然是，此查询在其他应用程序中的运行速度要快得多.我在这里遗漏了什么，为什么无论我尝试什么，相同的查询都会运行得这么慢?

I know this could probably be re-written using JOINS and indexing to speed it up, but the fact remains that this query works significantly faster from other apps. What am I missing here as to why the same query runs so much slower no matter what I try?

更新: 似乎 sqlite 的版本与问题有关.使用旧的 System.Data.Sqlite v1.0.66.0 查询就像其他应用程序一样运行，但是使用更新的版本很慢.我还没有确定具体在哪个版本发生了变化，但我很确定这与底层的 sqlite3 版本有关，而不是专门针对 System.Data.Sqlite.如果有人知道在这种情况下可能发生了什么变化会导致子查询速度变慢，或者如果有设置或某些东西可以使新版本的 sqlite 中的子查询运行得更快，请告诉我！

UPDATE: It seems the version of sqlite has something to do with the issue. Using the legacy System.Data.Sqlite v1.0.66.0 the query runs just like the other apps, however using a more recent version it is slow. I haven't pinpointed what at what version exactly this changed, but am pretty sure it's to do with the underlying sqlite3 version not System.Data.Sqlite specifically. If anyone knows what could have changed that would cause subqueries to slow down so much in this situation, or if there are settings or something that can make subqueries run faster in new versions of sqlite please let me know!

同样，该查询是一个示例，并不理想且部分冗余......问题更多是关于为什么它在一个而不是另一个中起作用.

Again, the query is an example and is not ideal and partially redundant... the question is more about why it works in one and not the other.

提前感谢您提供任何额外的意见！

Thanks in advance for any additional input!

更新:已解决

请看下面我的回答.

推荐答案

好吧，原来这与 SQLite 1.7.0 引入的自动索引有关.在我的情况下，在这种没有索引的表上使用子查询意味着 SQLite 创建自动索引所花费的时间会导致查询遇到的额外开销.

Ok turns out it was to do with Automatic Indexing, which was introduced with SQLite 1.7.0. In my situation using subqueries on this kind of table without indexes meant that the time it took SQLite to create the automatic indexes was causing the additional overhead that the queries were experiencing.

解决方案是使用:

PRAGMA automatic_index=OFF;

在任何使用IN"子句的查询的开头.

at the start of any query that uses the "IN" clause.

在列上创建索引也可以解决这个问题(未经测试)，但是在这种特殊情况下，创建索引所需的额外大小/磁盘使用是不值得的.

Creating indexes on the columns may also solve this (untested), however in this particular situation the additional size/disk usage necessary to create the indexes is not worth it.

这也表明我使用的 LinqPad SQLite 插件和数据库查看器基于旧的 sqlite 版本.

This would also suggest that the LinqPad SQLite plugin and the database viewers I was using are based on old sqlite versions.

更多信息请访问:

http://www.sqlite.org/src/info/8011086c85c6c4040

http://www.sqlite.org/optoverview.html#autoindex

感谢所有回复的人.

这篇关于使用较新版本的 System.Data.SQLite/sqlite3.dll 在 SQLite 数据库上使用子查询进行查询大约慢 10 倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！