Python中的Sqlite3模块SELECT速度远低于Shell

Question

我在用Python的sqlite3模块，但发现某个SELECT查询的速度非常慢，相比在命令行中直接运行这个查询。首先要说的是，两个版本的sqlite3都是3.7.17。

我的查询是

SELECT r.ID, r.Date FROM my_table r
WHERE
r.Date IN (SELECT Date FROM my_table WHERE ID = r.ID GROUP BY Date LIMIT 2);

Python代码是

con = lite.connect(path_to_database)
cur = con.cursor()

with con:
    cur.execute(sql_query)

其中sql_query是一个字符串变量，里面包含了最初的查询。

我猜问题出在优化IN子查询上。

性能细节：my_table里有167000条记录，在命令行中运行这个查询大约需要10秒，而在Python中运行则超过5分钟（我在这个时间点就停止了）。

目前因为是创建表，我只是把代码复制粘贴到命令行作为临时解决办法，我该怎么做才能从Python中运行这个查询呢？

补充

当我运行EXPLAIN QUERY PLAN时，得到的结果是：

命令行：

0           0           0           SCAN TABLE PIT_10_Days AS r (~500000 rows)
0           0           0           EXECUTE CORRELATED LIST SUBQUERY 1
1           0           0           SEARCH TABLE PIT_10_Days USING AUTOMATIC C
1           0           0           USE TEMP B-TREE FOR GROUP BY

Python：

0           0           TABLE PIT_10_Days AS r 
0           0           TABLE PIT_10_Days

我不确定这个差异是因为在Python中获取EXPLAIN QUERY PLAN时出现的问题，还是说这本身就是问题所在。

Answer 1

抱歉回复得这么晚，我刚刚才看到这个问题。
很遗憾，我不知道为什么sqlite3模块的表现和命令行不一样，但你可以尝试从一开始就避免使用相关查询。我不确定它是否总是能按照你的想法来，因为你在子查询中没有对结果进行排序。

我猜你是想要每个ID的最新两个日期？试试这个：

SELECT r.ID AS ID, max( r.Date ) AS Date
 FROM my_table AS r
 GROUP BY r.ID

UNION

SELECT r.ID, max( r.Date )
  FROM 
      my_table AS r
      JOIN ( 
        SELECT ID,
               max( Date ) AS Date
          FROM my_table
         GROUP BY ID) AS maxDat
      ON
      r.ID = maxDat.ID AND
      r.Date != maxDat.Date
 GROUP BY r.ID;

这个查询会选出每个ID及其最新的日期。然后，它会把这个结果和另一个表中的类似选择合并，从中取出实际的最新日期，这样你就能得到第二个最新的日期。如果你需要的不止是最新的两个日期，这样的做法会变得相当麻烦，但如果只要两个日期，这样做应该没问题，而且可能会快很多。