使用较新版本的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应用程序中查询大约500,000行的非常简单的Sqlite数据表时(约5秒)，我遇到了查询性能降低的问题.

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!

更新:已解决

请在下面查看我的答案.

See my answer below.

推荐答案

好吧，事实证明这与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屋！