sqlite3 performance problems only in python
stef.mientki at gmail.com
Thu Jul 23 09:02:45 EDT 2009
Tim Chase wrote:
>> until now I used only small / simple databases in Python with sqlite3.
>> Now I've a large and rather complex database.
>> The most simple query (with just a result of 100 rows),
>> takes about 70 seconds.
>> And all that time is consumed in "cursor.fetchall"
>> Using the same database in Delphi,
>> using the same query,
>> takes less than 5 seconds (including displaying the full table in a
> While it may seem obvious, are you doing anything time-consuming with
> those results? Or have you tested just doing the fetchall() without
> doing any further processing? I'm curious on the timing of
> sql = "..."
> start = time()
> rows = cursor.fetchall()
> end = time()
> print end-start
No this is exactly what I did,
I timed the execute and fetchall seperatly:
execute: 125 msec
fetchall: 71000 msec (returning 100 rows and 25 columns)
pysqlite: version 2.3.2
btw, I don't know if it's of any importance, the SQL-statement I perform is
select OPNAMEN.*, NAME, NAME_, SCORES.SCORE, PATIENT.*
inner join POID_VLID on OPNAMEN.POID =
inner join VRAAGLST on VRAAGLST.VLID =
inner join VLID_SSID on VRAAGLST.VLID =
inner join SUBSCHAAL_GEGEVENS on SUBSCHAAL_GEGEVENS.SSID =
inner join POID_SSID_SCID on ( OPNAMEN.POID =
POID_SSID_SCID.POID ) and
( SUBSCHAAL_GEGEVENS.SSID =
inner join SCORES on SCORES.SCID =
inner join PID_POID on OPNAMEN.POID = PID_POID.POID
inner join PATIENT on PATIENT.PID = PID_POID.PID
where substr ( lower( NAME) , 1, 6) = 'cis20r'
and lower ( NAME_ ) = 'fatigue'
and TEST_COUNT in (3,4)
and DATETIME > 39814.0
and SCORE < 30
> with no other processing. I regularly write sql that's fairly complex
> and brings back somewhat large datasets (sometimes in sqlite), and
> have never experienced problems with "simple quer[ies] (with just a
> result of 100 rows" taking such extrordinary times
> The answer from the above code will help determine whether it's the
> sqlite portion that's crazy (and might need some well-placed index
> statements; though if your Delphi code is fine, I suspect not), or if
> it's your application code that goes off into left field with the
> resulting data.
More information about the Python-list