为什么python+sqlite3非常慢? [英] Why python+sqlite3 is extremely slow?

问题描述

我尝试使用Python 2.7.4 + sqlite3"和Firefox SQLite Manager 0.8.0"处理对同一数据库的相同请求.

在小型数据库(8000 条记录)上，Python 和 Firefox 运行速度快，结果相同.

在更大的数据库(2600000 条记录)上:

• SQLite Manager 在 28 秒内处理了数据库(24 条记录)
• Python 程序已经运行了 20 分钟没有任何结果

下面的程序可能有什么问题，所以python sqlite3无法在合理的时间内处理查询，而可以更快地处理相同的请求?

导入 sqlite3_sql1 = """SELECT DISTINCT J2.rule_description,J2.feature_type，J2.action_item_id,J2.rule_items来自期刊 J1，期刊J2J1.base = J2.baseAND J1.action_item_id=J2.action_item_idAND J1.type="禁止操作"AND J2.type="已应用操作"AND J1.rule_description="一些测试规则"AND J1.action_item_id IN (1, 2, 3, 14, 15, 16, 17, 18, 19, 30, 31, 32)"""如果 __name__ == '__main__':sqlite_output = r'D:\results.sqlite'以 sqlite3.connect(sqlite_output) 作为连接:对于 connection.execute(_sql1) 中的行:打印行

更新: SQLite 的命令行 Shell 也返回相同的 24 条记录

UPDATE2: sqlite3.sqlite_version 是 '3.6.21'

解决方案

看来，问题与 Python 2.7 附带的旧版本 sqlite 有关.在 python 3.3 中一切正常.

非常感谢@CL 的精彩评论！

在 python 2.7 中

<预><代码>>>>导入 sqlite3>>>sqlite3.sqlite_version'3.6.21'

在 python 3.3 中

<预><代码>>>>导入 sqlite3>>>sqlite3.sqlite_version'3.7.12'

I tried to process the same request to the same database using "Python 2.7.4 + sqlite3" and "Firefox SQLite Manager 0.8.0".

On the tiny database (8000 records) both Python and Firefox work fast and give the same result.

On the bigger database (2600000 records):

• SQLite Manager processed database in 28seconds (24 records)
• Python program is working already for 20 minutes without any result

What can be wrong with the following program, so python sqlite3 cannot process the query in reasonable time, while the same request can be processed faster?

import sqlite3

_sql1 = """SELECT DISTINCT J2.rule_description,
                J2.feature_type,
                J2.action_item_id,
                J2.rule_items
FROM journal J1,
     journal J2
WHERE J1.base = J2.base
    AND J1.action_item_id=J2.action_item_id
    AND J1.type="Action disabled"
    AND J2.type="Action applied"
    AND J1.rule_description="Some test rule"
    AND J1.action_item_id IN (1, 2, 3, 14, 15, 16, 17, 18, 19, 30, 31, 32)
"""

if __name__ == '__main__':
    sqlite_output = r'D:\results.sqlite'
    with sqlite3.connect(sqlite_output) as connection:
        for row in connection.execute(_sql1):
            print row

UPDATE: Command Line Shell For SQLite also returns the same 24 records

UPDATE2: sqlite3.sqlite_version is '3.6.21'

解决方案

It seems, that the problem is related with the old version of sqlite that shipped with Python 2.7. Everything works fine in python 3.3.

Thanks a lot to @CL for the great comment!

In python 2.7

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.6.21'

In python 3.3

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.7.12'

这篇关于为什么python+sqlite3非常慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！