sqlite3 performance problems only in python
Che M
cmpython at gmail.com
Thu Jul 23 17:54:34 EDT 2009
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.
Why do you use pysqlite? I just import sqlite3 in Python 2.5.
What is the advantage of pysqlite?
Che
More information about the Python-list
mailing list