CODEWITHSUNDEEP
sqlsqlite
cdhowie
cdhowie

Reputation: 169018

Disparate performance between SQLite command-line client and SQLite library for the same query

I've been working on a performance-critical application, and one of the queries involved has this structure (names have been changed, but nothing else):

SELECT x.* FROM a
LEFT OUTER JOIN (
    SELECT c_a, c_b, c_c
    FROM b
    UNION ALL
    SELECT c_a, c_b, c_c
    FROM c
) AS x
ON a.c_a = x.c_a
WHERE a.c_d = ?
ORDER BY a.c_a

I have run EXPLAIN QUERY PLAN on this query and all indexes seem to be used appropriately. Using the command-line client, this query completes in about 0.75 seconds. This is about what I would expect given the amount of data in the database.

However, the same query executed through the SQLite library gives dramatically different behavior. Profiler data shows that for one execution of this query, the time spent in the sqlite3_step() function is about 120 seconds. This sub-second query is taking two minutes with the library, and I have absolutely no explanation as to why.

The database file being operated on in both cases is byte-for-byte identical.

The SQLite library is version 3.7.2, and the command-line client is version 3.7.13. I don't know if this may be a factor, but it seems unlikely to me.

Things I have tried:

Upvotes: 2

Views: 221

Answers (2)

cdhowie
cdhowie

Reputation: 169018

To provide closure for observers and participants, upgrading to SQLite 3.7.13 appears to have solved this problem; now 1.5 seconds are spent in sqlite3_step() which is obviously a dramatic improvement. 3.7.2 appears to have some bug or missing optimization that inflated the time required to execute the query.

Other instances of weird behavior also included delays that were a multiple of 60 seconds. This may be indicative of a lock-related bug in 3.7.2 (See EthanB's answer) that has since been resolved.

Upvotes: 1

EthanB
EthanB

Reputation: 4289

Is the command-line client locking the database when the SQLite library tries to run? I seem to recall 60 seconds being a SQLite query timeout (which I ran into with an NHibernate client).

Upvotes: 0

Related Questions