CODEWITHSUNDEEP
performancepython-2.7python-3.xsqlite
Konstantin Tenzin
Konstantin Tenzin

Reputation: 12908

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):

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'

Upvotes: 11

Views: 11648

Answers (1)

Konstantin Tenzin
Konstantin Tenzin

Reputation: 12908

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'

Upvotes: 7

Related Questions