运行sqlite查询后如何加快获取结果的速度? [英] How can I speed up fetching the results after running an sqlite query?

问题描述

作为对我问题的回答:正常吗sqlite.fetchall() 这么慢? 似乎 fetch-all 和 fetch-one 对于 sqlite 可能非常慢.

As an answer on my question: Is it normal that sqlite.fetchall() is so slow? it seems that fetch-all and fetch-one can be incredibly slow for sqlite.

正如我在那里提到的，我有以下查询:

As I mentioned there, I have the following query:

time0 = time.time()
self.cursor.execute("SELECT spectrum_id, feature_table_id "+
                "FROM spectrum AS s "+
                "INNER JOIN feature AS f "+
                "ON f.msrun_msrun_id = s.msrun_msrun_id "+
                "INNER JOIN (SELECT feature_feature_table_id, min(rt) AS rtMin, max(rt) AS rtMax, min(mz) AS mzMin, max(mz) as mzMax "+
                             "FROM convexhull GROUP BY feature_feature_table_id) AS t "+
                "ON t.feature_feature_table_id = f.feature_table_id "+
                "WHERE s.msrun_msrun_id = ? "+
                "AND s.scan_start_time >= t.rtMin "+
                "AND s.scan_start_time <= t.rtMax "+
                "AND base_peak_mz >= t.mzMin "+
                "AND base_peak_mz <= t.mzMax", spectrumFeature_InputValues)
print 'query took:',time.time()-time0,'seconds'

time0 = time.time()
spectrumAndFeature_ids = self.cursor.fetchall()      
print time.time()-time0,'seconds since to fetchall'

select 语句的执行大约需要 50 秒(可以接受).然而，fetchall() 需要 788 秒，只获取 981 个结果.

The execution of the select statement takes about 50 seconds (acceptable). However, the fetchall() takes 788 seconds, only fetching 981 results.

建议加快查询的方法作为我的问题的答案:sqlite.fetchall() 这么慢正常吗? 使用fetchmany()，并没有提高获取结果的速度.

The way proposed to speed up the query given as answer to my question: Is it normal that sqlite.fetchall() is so slow? using fetchmany(), has not improved the speed of fetching the results.

如何在运行 sqlite 查询后加快获取结果的速度?

How can I speed up fetching the results after running an sqlite query?

与我试图在命令行上执行的完全一样的 sql:

The sql exactly as I tried to execute it on command line:

sqlite> SELECT spectrum_id, feature_table_id
   ...> FROM spectrum AS s 
   ...> INNER JOIN feature AS f 
   ...> ON f.msrun_msrun_id = s.msrun_msrun_id 
   ...> INNER JOIN (SELECT feature_feature_table_id, min(rt) AS rtMin, max(rt) AS rtMax, min(mz) AS mzMin, max(mz) as mzMax 
   ...> FROM convexhull GROUP BY feature_feature_table_id) AS t 
   ...> ON t.feature_feature_table_id = f.feature_table_id 
   ...> WHERE s.msrun_msrun_id = 1
   ...> AND s.scan_start_time >= t.rtMin
   ...> AND s.scan_start_time <= t.rtMax
   ...> AND base_peak_mz >= t.mzMin
   ...> AND base_peak_mz <= t.mzMax;

<小时>

更新:

所以我大约 45 分钟前开始在命令行上运行查询，它仍然很忙，所以使用命令行也很慢.

So I started running the query on the commandline about 45 minutes ago, and it's still busy, so it's also very slow using the commandline.

推荐答案

来自阅读 这个问题，听起来您可以从使用 APSW sqlite 模块.不知何故，您可能是您的 sqlite 模块的受害者，导致您的查询以某种性能较低的方式执行.

From reading this question, it sounds like you could benefit from using the APSW sqlite module. Somehow you may be victim of your sqlite module causing your query to be executed in some less performant manner.

我很好奇所以我自己尝试使用 apsw.这并不太复杂.为什么不试试看呢?

I was curious so I tried using apsw myself. It wasn't too complicated. Why don't you give it a try?

要安装它，我必须:

1. 提取最新版本.

2. 让安装包获取最新的 sqlite 合并.

1. Extract the latest version.

2. Have the installation package fetch the latest sqlite amalgamation.

python setup.py fetch --sqlite

构建和安装.

Build and install.

sudo python setup.py install

使用它代替其他 sqlite 模块.

Use it in place of the other sqlite module.

import apsw
<...>
conn = apsw.Connection('foo.db')

这篇关于运行sqlite查询后如何加快获取结果的速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！