Why is python + sqlite3 extremely slow?

Question

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

In a tiny database (8,000 records), both Python and Firefox are fast and produce the same result.

In a large database (2600000 records):

• SQLite Manager processed the database in 28 seconds (24 records)
• The Python program has been running for 20 minutes without any results.

What could be wrong in the next program, so python sqlite3 cannot process the query in a reasonable amount of time, while the same query 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: Shell command line for SQLite also returns the same 24 records

UPDATE2: sqlite3.sqlite_version - "3.6.21"

Answer 1

The problem seems to be related to the old version of sqlite shipped with Python 2.7. Everything works fine in python 3.3.

Thanks a lot @CL for a 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'