Thread: [SQLObject] howto improve select() performance
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
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> |
From: Oleg B. <ph...@ph...> - 2006-09-25 08:59:25
|
On Mon, Sep 25, 2006 at 10:26:29AM +0200, Didrik Pinte wrote: > class sqlmeta: > # cacheValues is needed as soons as we need transactions and > concurrent access to the database > cacheValues = False Set cacheValues to True and retry. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Didrik P. <dp...@it...> - 2006-09-25 13:07:43
|
Le lundi 25 septembre 2006 =E0 12:59 +0400, Oleg Broytmann a =E9crit : > On Mon, Sep 25, 2006 at 10:26:29AM +0200, Didrik Pinte wrote: > > class sqlmeta: > > # cacheValues is needed as soons as we need transactions and > > concurrent access to the database > > cacheValues =3D False >=20 > Set cacheValues to True and retry. >=20 > Oleg. I've tried with "cacheValues =3D True", it changes something but it still a problem. With the cacheValues, it spares all the specific queries for each row of the table but it does a "select * from rawdata". This seems to be translated in getting all the objects into memory. My test table counts only 800.000 rows and memory usage is not efficient at all. Here is the way it behaves with the cacheValues set to True : >>> rawdata.sqlmeta.cacheValues True >>> 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 <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> <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> <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> <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> <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> <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> <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> <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> <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> >>> I'm running with sqlobject 0.7.1-1 (debian/sid version). The backend is a Postgres 8.1 database. Didrik |
From: Oleg B. <ph...@ph...> - 2006-09-25 14:26:09
|
On Mon, Sep 25, 2006 at 03:07:41PM +0200, Didrik Pinte wrote: > it does a "select * from rawdata". If you want to do .select() but don't want to draw all objects at once use .select().lazyIter(). I am not sure though if SQLObject will properly clear the cache. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Didrik P. <dp...@it...> - 2006-09-28 16:45:48
|
Le lundi 25 septembre 2006 =E0 18:25 +0400, Oleg Broytmann a =E9crit : > On Mon, Sep 25, 2006 at 03:07:41PM +0200, Didrik Pinte wrote: > > it does a "select * from rawdata". >=20 > If you want to do .select() but don't want to draw all objects at once > use .select().lazyIter(). I am not sure though if SQLObject will properly > clear the cache. >=20 > Oleg. Hi Oleg,=20 Thank you for the hint but it does not seem to change anything. I've made a little benchmark with the following code : -------------------------------------------------------------------- import time from db.dbcon import * from db.dbscheme import * import psycopg # PSYCOPG DEFAULT TEST psycopg_start =3D time.time() dbcon =3D psycopg.connect('dbname=3D***** user=3D***** password=3D*****') dbcur =3D dbcon.cursor() dbcur.execute('select * from rawdata') psycopg_valc =3D 0 for val in dbcur.fetchall(): psycopg_valc +=3D 1 dbcur.close() psycopg_end =3D time.time() # SQLOBJECT DEFAULT TEST sqlobject_start =3D time.time() res =3D rawdata.select() sqlobject_valc =3D 0 for val in res: sqlobject_valc +=3D 1 pass sqlobject_end =3D time.time() print "SQLObject duration %s" % float(sqlobject_end - sqlobject_start) print "Psycopg duration %s" % float(psycopg_end - psycopg_start) -------------------------------------------------------------------- Here is the result : SQLObject duration 20.2379980087 Psycopg duration 2.84033989906 The problem is not really the execution time but the memory consumption. Isn't there a way of getting kind of iterator using the sqlobject syntax ?=20 Didrik |
From: Oleg B. <ph...@ph...> - 2006-09-28 16:57:57
|
On Thu, Sep 28, 2006 at 06:45:46PM +0200, Didrik Pinte wrote: > # SQLOBJECT DEFAULT TEST > > sqlobject_start = time.time() > res = rawdata.select() > sqlobject_valc = 0 > for val in res: > sqlobject_valc += 1 > pass > sqlobject_end = time.time() I don't see any .lazyIter() here. > The problem is not really the execution time but the memory consumption. > Isn't there a way of getting kind of iterator using the sqlobject > syntax ? .select() returns a SelectResults object which *is* an iterator. But default .__iter__() draws all rows at once. .lazyIter() draws rows in batches. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Didrik P. <dp...@it...> - 2006-09-28 21:15:23
|
Le jeudi 28 septembre 2006 =E0 20:57 +0400, Oleg Broytmann a =E9crit : > On Thu, Sep 28, 2006 at 06:45:46PM +0200, Didrik Pinte wrote: > > # SQLOBJECT DEFAULT TEST > >=20 > > sqlobject_start =3D time.time() > > res =3D rawdata.select() > > sqlobject_valc =3D 0 > > for val in res: > > sqlobject_valc +=3D 1 > > pass > > sqlobject_end =3D time.time() >=20 > I don't see any .lazyIter() here. Sorry, I went a bit too quickly and post the first test case.=20 Here is the second witht the lazyIter(). It spares 5% ofthe time but it still 9 times slower than a direct selection : --------------------------------------------------------------------------- import time from db.dbcon import * from db.dbscheme import * import psycopg # PSYCOPG DEFAULT TEST psycopg_start =3D time.time() dbcon =3D psycopg.connect('dbname=3D***** user=3D***** password=3D*****') dbcur =3D dbcon.cursor() dbcur.execute('select * from rawdata') psycopg_valc =3D 0 for val in dbcur.fetchall(): psycopg_valc +=3D 1 dbcur.close() psycopg_end =3D time.time() # SQLOBJECT DEFAULT TEST sqlobject_start =3D time.time() res =3D rawdata.select().lazyIter() sqlobject_valc =3D 0 for val in res: sqlobject_valc +=3D 1 pass sqlobject_end =3D time.time() print "SQLObject duration %s" % float(sqlobject_end - sqlobject_start) print "Psycopg duration %s" % float(psycopg_end - psycopg_start) --------------------------------------------------------------------------- Result is the following : SQLObject duration 18.6353540421 Psycopg duration 2.944272995 |
From: Oleg B. <ph...@ph...> - 2006-09-29 10:48:45
|
On Thu, Sep 28, 2006 at 11:15:23PM +0200, Didrik Pinte wrote: > sqlobject_start = time.time() > res = rawdata.select().lazyIter() > sqlobject_valc = 0 > for val in res: > sqlobject_valc += 1 > pass > > sqlobject_end = time.time() If you are still sure the problem is in the memory consumption, you can try to periodically clean the cache: res = rawdata.select().lazyIter() sqlobject_valc = 0 threshold = 1000 for val in res: sqlobject_valc += 1 if sqlobject_valc >= threshold: rawdata._connection.cache.clear() sqlobject_valc = 0 (And the next thing to try would be to draw raws by batches. If you could not come up with code - I will show you the pattern that works for me pretty good.) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |