Tim Chase wrote:
until now I used only small / simple databases in Python with sqlite3.
Now I've a large and rather complex database.
The most simple query (with just a result of 100 rows),
takes about 70 seconds.
And all that time is consumed in "cursor.fetchall"
Using the same database in Delphi,
using the same query,
takes less than 5 seconds (including displaying the full table in a
grid).
While it may seem obvious, are you doing anything time-consuming with
those results? Or have you tested just doing the fetchall() without
doing any further processing? I'm curious on the timing of
sql = "..."
start = time()
cursor.execute(sql)
rows = cursor.fetchall()
end = time()
print end-start
No this is exactly what I did,
I timed the execute and fetchall seperatly:
execute: 125 msec
fetchall: 71000 msec (returning 100 rows and 25 columns)
pysqlite: version 2.3.2
btw, I don't know if it's of any importance, the SQL-statement I perform is
select OPNAMEN.*, NAME, NAME_, SCORES.SCORE, PATIENT.*
from OPNAMEN
inner join POID_VLID on OPNAMEN.POID =
POID_VLID.POID
inner join VRAAGLST on VRAAGLST.VLID =
POID_VLID.VLID
inner join VLID_SSID on VRAAGLST.VLID =
VLID_SSID.VLID
inner join SUBSCHAAL_GEGEVENS on SUBSCHAAL_GEGEVENS.SSID =
VLID_SSID.SSID
inner join POID_SSID_SCID on ( OPNAMEN.POID =
POID_SSID_SCID.POID ) and
( SUBSCHAAL_GEGEVENS.SSID =
POID_SSID_SCID.SSID )
inner join SCORES on SCORES.SCID =
POID_SSID_SCID.SCID
inner join PID_POID on OPNAMEN.POID = PID_POID.POID
inner join PATIENT on PATIENT.PID = PID_POID.PID
where substr ( lower( NAME) , 1, 6) = 'cis20r'
and lower ( NAME_ ) = 'fatigue'
and TEST_COUNT in (3,4)
and DATETIME > 39814.0
and SCORE < 30
cheers,
Stef
with no other processing. I regularly write sql that's fairly complex
and brings back somewhat large datasets (sometimes in sqlite), and
have never experienced problems with "simple quer[ies] (with just a
result of 100 rows" taking such extrordinary times
The answer from the above code will help determine whether it's the
sqlite portion that's crazy (and might need some well-placed index
statements; though if your Delphi code is fine, I suspect not), or if
it's your application code that goes off into left field with the
resulting data.
-tkc
--
http://mail.python.org/mailman/listinfo/python-list
