[SQLObject] howto improve select() performance
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
[SQLObject] howto improve select() performance
|
From: Didrik P. <dp...@it...> - 2006-09-25 08:26:37
|
Hi,
I'm trying to improve the performance of the statements described below.
I'm simply trying to parse the content of a "sqlobject table" using a
select() method call without any parameters. This seems to :
[1] select * from the table (why ?)
[2] for each row select every value of the row with a distinct query
Because my table aren't really small, this takes a very long time.=20
- Isn't it possible to have a behaviour like just getting the next value
of the resultset without parsing all the table before ?
Thank you for all the interesting suggestion you can have ;-)
Didrik
--------------------------------------------------------------------------
class rawdata(SQLObject):
'''
Class describing a generic table for agro-environmental datasets (raw
values)
'''
=20
class sqlmeta:
# cacheValues is needed as soons as we need transactions and
concurrent access to the database
cacheValues =3D False
mtime =3D DateTimeCol(notNone=3DTrue)
station =3D ForeignKey('stations',notNone=3DTrue)
tsa =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
tha =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
hra =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
tsf =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
tss =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
ens =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
dvt =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
vvt =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
plu =3D DecimalCol(size=3D6,precision=3D1,default=3DNone)
>>> for a in rawdata.select():
... if a.id < 10:
... print a
...
1/Select : SELECT rawdata.id, rawdata.mtime, rawdata.station_id,
rawdata.tsa, rawdata.tha, rawdata.hra, rawdata.tsf, rawdata.tss,
rawdata.ens, rawdata.dvt, rawdata.vvt, rawdata.plu FROM rawdata WHERE 1
=3D 1
1/QueryR : SELECT rawdata.id, rawdata.mtime, rawdata.station_id,
rawdata.tsa, rawdata.tha, rawdata.hra, rawdata.tsf, rawdata.tss,
rawdata.ens, rawdata.dvt, rawdata.vvt, rawdata.plu FROM rawdata WHERE 1
=3D 1
1/COMMIT : auto
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (1)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (1)
1/COMMIT : auto
<rawdata 1 mtime=3D'datetime.datetime...)' stationID=3D1
tsa=3D1.8999999999999999 tha=3D1.8999999999999999 hra=3D99.799999999999997
tsf=3D-0.20000000000000001 tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone
vvt=3D-999.0 plu=3D0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (2)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (2)
1/COMMIT : auto
<rawdata 2 mtime=3D'datetime.datetime...)' stationID=3D1 tsa=3D2.0 tha=3D2.=
0
hra=3D99.799999999999997 tsf=3D-0.20000000000000001 tss=3D1.399999999999999=
9
ens=3D0.0 dvt=3DNone vvt=3D1.3 plu=3D0.10000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (3)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (3)
1/COMMIT : auto
<rawdata 3 mtime=3D'datetime.datetime...)' stationID=3D1
tsa=3D2.1000000000000001 tha=3D2.1000000000000001 hra=3D99.799999999999997
tsf=3D-0.20000000000000001 tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone
vvt=3D1.6000000000000001 plu=3D0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (4)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (4)
1/COMMIT : auto
<rawdata 4 mtime=3D'datetime.datetime...)' stationID=3D1 tsa=3D1.7
tha=3D1.6000000000000001 hra=3D97.900000000000006 tsf=3D-0.2000000000000000=
1
tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone vvt=3D1.5 plu=3D0.29999999999=
999999>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (5)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (5)
1/COMMIT : auto
<rawdata 5 mtime=3D'datetime.datetime...)' stationID=3D1
tsa=3D1.3999999999999999 tha=3D1.3999999999999999 hra=3D99.799999999999997
tsf=3D-0.20000000000000001 tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone vv=
t=3D1.5
plu=3D0.59999999999999998>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (6)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (6)
1/COMMIT : auto
<rawdata 6 mtime=3D'datetime.datetime...)' stationID=3D1
tsa=3D1.3999999999999999 tha=3D1.3999999999999999 hra=3D99.799999999999997
tsf=3D-0.20000000000000001 tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone vv=
t=3D1.5
plu=3D0.20000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (7)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (7)
1/COMMIT : auto
<rawdata 7 mtime=3D'datetime.datetime...)' stationID=3D1 tsa=3D1.5 tha=3D1.=
5
hra=3D99.799999999999997 tsf=3D-0.20000000000000001 tss=3D1.399999999999999=
9
ens=3D0.0 dvt=3DNone vvt=3D1.8999999999999999 plu=3D0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (8)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (8)
1/COMMIT : auto
<rawdata 8 mtime=3D'datetime.datetime...)' stationID=3D1 tsa=3D1.5 tha=3D1.=
5
hra=3D99.799999999999997 tsf=3D-0.20000000000000001 tss=3D1.399999999999999=
9
ens=3D0.0 dvt=3DNone vvt=3D1.5 plu=3D0.20000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT mtime FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT station_id FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT tsa FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT tha FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT hra FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT tsf FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT tss FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT ens FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT dvt FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT vvt FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id =3D (9)
1/QueryR : SELECT plu FROM rawdata WHERE id =3D (9)
1/COMMIT : auto
<rawdata 9 mtime=3D'datetime.datetime...)' stationID=3D1
tsa=3D1.3999999999999999 tha=3D1.3999999999999999 hra=3D99.799999999999997
tsf=3D-0.20000000000000001 tss=3D1.3999999999999999 ens=3D0.0 dvt=3DNone vv=
t=3D1.8
plu=3D0.29999999999999999>
|