作为对我的问题的答案: 它是正常的,sqlite.fetchall()是如此之慢? 似乎取全部和获取 - 一个可以为sqlite的慢得令人难以置信。
正如我所说,我有以下查询:
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'
选择语句的执行大约需要50秒(可接受的)。 然而,使用fetchall()接受788秒,只取981个结果。
建议加快给出回答我的问题查询方式: 它是正常的,sqlite.fetchall()是如此之慢? 使用支持fetchmany(),并没有得到改善提取的结果的速度。
我如何可以加快运行SQLite的查询之后获取的结果?
正是因为我试图执行它的命令行的SQL语句:
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分钟前,它仍然是繁忙的,所以它使用命令行也是很慢的。