Piet van Oostrum wrote:
Stef Mientki <stef.mien...@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
--
http://mail.python.org/mailman/listinfo/python-list