我如何可以加快运行SQLite的查询之后获取的结果?(How can I speed up fetc

2019-06-24 02:43发布

作为对我的问题的答案: 它是正常的,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分钟前,它仍然是繁忙的,所以它使用命令行也是很慢的。

Answer 1:

从阅读这个问题 ,这听起来像你可以从使用中获益APSW sqlite的模块 。 不知怎的,你可能是你的sqlite模块的受害者造成一些不太高性能的方式执行查询。

我很好奇,所以我试图用自己APSW。 这是不是太复杂。 你为什么不试一试?

要安装它,我必须:

  1. 提取最新版本 。
  2. 有安装包获取最新的sqlite的融合。

     python setup.py fetch --sqlite 
  3. 构建和安装。

     sudo python setup.py install 
  4. 用它来代替其他sqlite的模块。

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


文章来源: How can I speed up fetching the results after running an sqlite query?