Che M wrote:
On Jul 23, 3:58 pm, Stef Mientki <stef.mien...@gmail.com> wrote:
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
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
--
http://mail.python.org/mailman/listinfo/python-list
