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.
- 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)
'''
class sqlmeta:
# cacheValues is needed as soons as we need transactions and
concurrent access to the database
cacheValues = False
mtime = DateTimeCol(notNone=True)
station = ForeignKey('stations',notNone=True)
tsa = DecimalCol(size=6,precision=1,default=None)
tha = DecimalCol(size=6,precision=1,default=None)
hra = DecimalCol(size=6,precision=1,default=None)
tsf = DecimalCol(size=6,precision=1,default=None)
tss = DecimalCol(size=6,precision=1,default=None)
ens = DecimalCol(size=6,precision=1,default=None)
dvt = DecimalCol(size=6,precision=1,default=None)
vvt = DecimalCol(size=6,precision=1,default=None)
plu = DecimalCol(size=6,precision=1,default=None)
>>> 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
= 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
= 1
1/COMMIT : auto
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (1)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (1)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (1)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (1)
1/QueryR : SELECT tha FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (1)
1/QueryR : SELECT hra FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (1)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (1)
1/QueryR : SELECT tss FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (1)
1/QueryR : SELECT ens FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (1)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (1)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (1)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (1)
1/QueryR : SELECT plu FROM rawdata WHERE id = (1)
1/COMMIT : auto
<rawdata 1 mtime='datetime.datetime...)' stationID=1
tsa=1.8999999999999999 tha=1.8999999999999999 hra=99.799999999999997
tsf=-0.20000000000000001 tss=1.3999999999999999 ens=0.0 dvt=None
vvt=-999.0 plu=0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (2)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (2)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (2)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (2)
1/QueryR : SELECT tha FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (2)
1/QueryR : SELECT hra FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (2)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (2)
1/QueryR : SELECT tss FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (2)
1/QueryR : SELECT ens FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (2)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (2)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (2)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (2)
1/QueryR : SELECT plu FROM rawdata WHERE id = (2)
1/COMMIT : auto
<rawdata 2 mtime='datetime.datetime...)' stationID=1 tsa=2.0 tha=2.0
hra=99.799999999999997 tsf=-0.20000000000000001 tss=1.3999999999999999
ens=0.0 dvt=None vvt=1.3 plu=0.10000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (3)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (3)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (3)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (3)
1/QueryR : SELECT tha FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (3)
1/QueryR : SELECT hra FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (3)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (3)
1/QueryR : SELECT tss FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (3)
1/QueryR : SELECT ens FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (3)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (3)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (3)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (3)
1/QueryR : SELECT plu FROM rawdata WHERE id = (3)
1/COMMIT : auto
<rawdata 3 mtime='datetime.datetime...)' stationID=1
tsa=2.1000000000000001 tha=2.1000000000000001 hra=99.799999999999997
tsf=-0.20000000000000001 tss=1.3999999999999999 ens=0.0 dvt=None
vvt=1.6000000000000001 plu=0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (4)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (4)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (4)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (4)
1/QueryR : SELECT tha FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (4)
1/QueryR : SELECT hra FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (4)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (4)
1/QueryR : SELECT tss FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (4)
1/QueryR : SELECT ens FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (4)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (4)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (4)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (4)
1/QueryR : SELECT plu FROM rawdata WHERE id = (4)
1/COMMIT : auto
<rawdata 4 mtime='datetime.datetime...)' stationID=1 tsa=1.7
tha=1.6000000000000001 hra=97.900000000000006 tsf=-0.20000000000000001
tss=1.3999999999999999 ens=0.0 dvt=None vvt=1.5 plu=0.29999999999999999>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (5)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (5)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (5)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (5)
1/QueryR : SELECT tha FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (5)
1/QueryR : SELECT hra FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (5)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (5)
1/QueryR : SELECT tss FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (5)
1/QueryR : SELECT ens FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (5)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (5)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (5)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (5)
1/QueryR : SELECT plu FROM rawdata WHERE id = (5)
1/COMMIT : auto
<rawdata 5 mtime='datetime.datetime...)' stationID=1
tsa=1.3999999999999999 tha=1.3999999999999999 hra=99.799999999999997
tsf=-0.20000000000000001 tss=1.3999999999999999 ens=0.0 dvt=None vvt=1.5
plu=0.59999999999999998>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (6)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (6)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (6)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (6)
1/QueryR : SELECT tha FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (6)
1/QueryR : SELECT hra FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (6)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (6)
1/QueryR : SELECT tss FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (6)
1/QueryR : SELECT ens FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (6)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (6)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (6)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (6)
1/QueryR : SELECT plu FROM rawdata WHERE id = (6)
1/COMMIT : auto
<rawdata 6 mtime='datetime.datetime...)' stationID=1
tsa=1.3999999999999999 tha=1.3999999999999999 hra=99.799999999999997
tsf=-0.20000000000000001 tss=1.3999999999999999 ens=0.0 dvt=None vvt=1.5
plu=0.20000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (7)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (7)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (7)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (7)
1/QueryR : SELECT tha FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (7)
1/QueryR : SELECT hra FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (7)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (7)
1/QueryR : SELECT tss FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (7)
1/QueryR : SELECT ens FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (7)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (7)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (7)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (7)
1/QueryR : SELECT plu FROM rawdata WHERE id = (7)
1/COMMIT : auto
<rawdata 7 mtime='datetime.datetime...)' stationID=1 tsa=1.5 tha=1.5
hra=99.799999999999997 tsf=-0.20000000000000001 tss=1.3999999999999999
ens=0.0 dvt=None vvt=1.8999999999999999 plu=0.0>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (8)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (8)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (8)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (8)
1/QueryR : SELECT tha FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (8)
1/QueryR : SELECT hra FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (8)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (8)
1/QueryR : SELECT tss FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (8)
1/QueryR : SELECT ens FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (8)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (8)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (8)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (8)
1/QueryR : SELECT plu FROM rawdata WHERE id = (8)
1/COMMIT : auto
<rawdata 8 mtime='datetime.datetime...)' stationID=1 tsa=1.5 tha=1.5
hra=99.799999999999997 tsf=-0.20000000000000001 tss=1.3999999999999999
ens=0.0 dvt=None vvt=1.5 plu=0.20000000000000001>
1/QueryOne: SELECT mtime FROM rawdata WHERE id = (9)
1/QueryR : SELECT mtime FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT station_id FROM rawdata WHERE id = (9)
1/QueryR : SELECT station_id FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT tsa FROM rawdata WHERE id = (9)
1/QueryR : SELECT tsa FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT tha FROM rawdata WHERE id = (9)
1/QueryR : SELECT tha FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT hra FROM rawdata WHERE id = (9)
1/QueryR : SELECT hra FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT tsf FROM rawdata WHERE id = (9)
1/QueryR : SELECT tsf FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT tss FROM rawdata WHERE id = (9)
1/QueryR : SELECT tss FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT ens FROM rawdata WHERE id = (9)
1/QueryR : SELECT ens FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT dvt FROM rawdata WHERE id = (9)
1/QueryR : SELECT dvt FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT vvt FROM rawdata WHERE id = (9)
1/QueryR : SELECT vvt FROM rawdata WHERE id = (9)
1/COMMIT : auto
1/QueryOne: SELECT plu FROM rawdata WHERE id = (9)
1/QueryR : SELECT plu FROM rawdata WHERE id = (9)
1/COMMIT : auto
<rawdata 9 mtime='datetime.datetime...)' stationID=1
tsa=1.3999999999999999 tha=1.3999999999999999 hra=99.799999999999997
tsf=-0.20000000000000001 tss=1.3999999999999999 ens=0.0 dvt=None vvt=1.8
plu=0.29999999999999999>
signature.asc
Description: Ceci est une partie de message numériquement signée
------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
