sqlite3 performance problems only in python

[Stef Mientki]

Piet van Oostrum wrote:
>>>>>> Stef Mientki <stef.mientki at gmail.com> (SM) wrote:
>>>>>>             
>
>   
>> SM> btw, I don't know if it's of any importance, the SQL-statement I perform is
>> SM> select OPNAMEN.*, NAME, NAME_, SCORES.SCORE, PATIENT.*
>> SM>  from OPNAMEN
>> SM>    inner join POID_VLID          on OPNAMEN.POID            = POID_VLID.POID
>> SM>    inner join VRAAGLST           on VRAAGLST.VLID           = POID_VLID.VLID
>> SM>    inner join VLID_SSID          on VRAAGLST.VLID           = VLID_SSID.VLID
>> SM>    inner join SUBSCHAAL_GEGEVENS on SUBSCHAAL_GEGEVENS.SSID = VLID_SSID.SSID
>> SM>    inner join POID_SSID_SCID     on ( OPNAMEN.POID            =
>> SM> POID_SSID_SCID.POID ) and
>> SM>                                     ( SUBSCHAAL_GEGEVENS.SSID =
>> SM> POID_SSID_SCID.SSID )
>> SM>    inner join SCORES             on SCORES.SCID             =
>> SM> POID_SSID_SCID.SCID
>> SM>    inner join PID_POID           on OPNAMEN.POID            = PID_POID.POID
>> SM>    inner join PATIENT            on PATIENT.PID             = PID_POID.PID
>> SM>  where substr ( lower( NAME) , 1, 6)  = 'cis20r'
>> SM>    and lower ( NAME_ ) = 'fatigue'
>> SM>    and TEST_COUNT in (3,4)
>> SM>    and DATETIME > 39814.0
>> SM>    and SCORE < 30
>>     
>
> 1) Do you have indices on the join fields?
>   
well I'm happily surprised, you came up with this suggestion
- I thought that sqlite created indexes on all primairy key and unique 
fields
- but after explicitly creating the indices, a gained a speed of about a 
factor 10
After checking the database creation, it seemed I forgot to make these 
fields the primary key
so thanks very much.

I gained another factor of 10 speed by updating to version 2.5.5 of 
pysqlite.

cheers,
Stef
> 2) Look at the ANALYZE command
> 3) Look at the EXPLAIN command
>