Why python+sqlite3 is extremely slow?

问题:

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'

回答1:

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'