sqlite3 performance problems only in python

[Stef Mientki]

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 
>> grid).
>
> 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()
>   cursor.execute(sql)
>   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.*
  from OPNAMEN
    inner join POID_VLID          on OPNAMEN.POID            = 
POID_VLID.POID
    inner join VRAAGLST           on VRAAGLST.VLID           = 
POID_VLID.VLID
    inner join VLID_SSID          on VRAAGLST.VLID           = 
VLID_SSID.VLID
    inner join SUBSCHAAL_GEGEVENS on SUBSCHAAL_GEGEVENS.SSID = 
VLID_SSID.SSID
    inner join POID_SSID_SCID     on ( OPNAMEN.POID            = 
POID_SSID_SCID.POID ) and
                                     ( SUBSCHAAL_GEGEVENS.SSID = 
POID_SSID_SCID.SSID )
    inner join SCORES             on SCORES.SCID             = 
POID_SSID_SCID.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

cheers,
Stef

>
> 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.
>
> -tkc
>
>
>
>