sqlite.fetchall() 这么慢正常吗? [英] Is it normal that sqlite.fetchall() is so slow?

问题描述

我有一个从两个内部连接表中选择的 sql 查询.select 语句的执行大约需要 50 秒.但是，fetchall() 需要 788 秒，并且只获取 981 个结果.这是查询和 fetchall 代码:

time0 = time.time()self.cursor.execute("选择频谱ID，特征表ID"+来自频谱AS s"+"INNER JOIN 功能 AS f"+"ON f.msrun_msrun_id = s.msrun_msrun_id "+内连接(选择特征_特征表_id，min(rt) AS rtMin，max(rt) AS rtMax，min(mz) AS mzMin，max(mz) as mzMax"+来自凸包 GROUP BY feature_feature_table_id)AS t"+"ON t.feature_feature_table_id = f.feature_table_id "+"哪里 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)打印 '查询需要:',time.time()-time0,'seconds'time0 = time.time()频谱AndFeature_ids = self.cursor.fetchall()打印 time.time()-time0,'seconds since to fetchall'

fetchall 需要这么长时间有什么原因吗?

<小时>

更新

正在做:

while 1:info = self.cursor.fetchone()如果信息:<做某事>别的:休息

和

一样慢

allInfo = self.cursor.fetchall()有关 allInfo 中的信息:<做某事>

解决方案

默认情况下 fetchall() 由于 与循环 fetchone() 一样慢Cursor 对象的 >arraysize 设置为 1.

为了加快速度，你可以循环fetchmany()，但要看到性能提升，你需要为它提供一个大于1的大小参数，否则它会获取many"按批次arraysize，即1.

很可能仅仅通过提高 arraysize 的值就可以获得性能提升，但我没有这样做的经验，所以你可能想先通过做类似的事情来试验一下:

<预><代码>>>>导入 sqlite3>>>conn = sqlite3.connect(":memory:")>>>cu = conn.cursor()>>>cu.arraysize1>>>cu.arraysize = 10>>>cu.arraysize10

关于以上内容的更多信息:http://docs.python.org/library/sqlite3.html#sqlite3.Cursor.fetchmany

I have an sql query that selects from two inner joined tables. The execution of the select statement takes about 50 seconds. However, the fetchall() takes 788 seconds and it only fetches 981 results. This is the query and fetchall code:

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'

Is there a reason why the fetchall takes so long?

---

update

Doing:

while 1:
    info = self.cursor.fetchone()
    if info:
        <do something>
    else:
        break

is going just as slow as

allInfo = self.cursor.fetchall()         
for info in allInfo:
    <do something>

解决方案

By default fetchall() is as slow as looping over fetchone() due to the arraysize of the Cursor object being set to 1.

To speed things up you can loop over fetchmany(), but to see a performance gain, you need to provide it with a size parameter bigger than 1, otherwise it'll fetch "many" by batches of arraysize, i.e. 1.

It is quite possible that you can get the performance gain simply by raising the value of arraysize, but I have no experience doing this, so you may want to experiment with that first by doing something like:

>>> import sqlite3
>>> conn = sqlite3.connect(":memory:")
>>> cu = conn.cursor()
>>> cu.arraysize
1
>>> cu.arraysize = 10
>>> cu.arraysize
10

More on the above here: http://docs.python.org/library/sqlite3.html#sqlite3.Cursor.fetchmany

这篇关于sqlite.fetchall() 这么慢正常吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！