sqlite3 performance problems only in python

[Stef Mientki]

Che M wrote:
> On Jul 23, 3:58 pm, Stef Mientki <stef.mien... at gmail.com> wrote:
>   
>> Piet van Oostrum wrote:
>>     
>>>>>>>> Stef Mientki <stef.mien... 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
>>>       
>>     
>
> You might want to consult the SQLite list for questions like this.
>   
thanks,
but because the same SQL-statement in Delphi performed well,
I thought it was a problem with the Python implementation.
> Why do you use pysqlite?  I just import sqlite3 in Python 2.5.
> What is the advantage of pysqlite?
>   
it's 10 .. 15 times faster then sqlite3 delivered with pyton 2.5.
AFAIK it's nothing different, just a newer version.

cheers,
Stef
> Che
>