Hi,
I'm trying to improve the performance of some modules in an application.
I'm facing the following problem with this snippet of code :
# get the validdata row
vdata =3D validdata.select(AND(validdata.q.stationID =3D=3D station.id,
validdata.q.mtime =3D=3D row.mtime))[0]
# compare and update states
for state in states:
# check state
current_state =3D getattr(vdata, state)
...
The tables are the following :
class validdata(SQLObject):
mtime =3D DateTimeCol(notNone=3DTrue)
station =3D ForeignKey('stations',notNone=3DTrue)=09
tha =3D DecimalCol( size=3D3,precision=3D1,default=3DNone)
tha_state =3D ForeignKey('validstates', notNone=3DTrue, default=3D1)
tsa =3D DecimalCol( size=3D3,precision=3D1,default=3DNone)
tsa_state =3D ForeignKey('validstates', notNone=3DTrue, default=3D1)
...
class validstates(SQLObject):
name =3D StringCol(alternateID=3DTrue, notNone=3DTrue, unique=3D=
True)
description =3D StringCol()
When parsing the validdata table to check for the states of the
different data attributes, I'm selecting the data for a specific
stationID and mtime with :
[1] vdata =3D validdata.select(AND(validdata.q.stationID =3D=3D station.id,
validdata.q.mtime =3D=3D row.mtime))[0]
then I parse the states in this row with the following code :
[2] for state in states:
getattr(vdata, state)
My understanding is that vdata is a SelectResults and that the
getatrr(vdata, state) will just fetch the data from this SelectResults.
It appears that I'm wrong.=20
It seems that for each getattr(vdata, state) there is a new call to the
database.
Here is the SQL output for expression [1] :
1/QueryR : SELECT validdata.id, validdata.mtime,
validdata.station_id, validdata.tsa, validdata.tsastate_id,
validdata.tha, validdata.thastate_id, validdata.hra,
validdata.hrastate_id, validdata.tsf, validdata.tsfstate_id,
validdata.tss, validdata.tssstate_id, validdata.ens,
validdata.ensstate_id, validdata.dvt, validdata.dvtstate_id,
validdata.vvt, validdata.vvtstate_id, validdata.plu,
validdata.plustate_id FROM validdata WHERE ((validdata.station_id =3D 1)
AND (validdata.mtime =3D '2006-01-04 05:00:00')) LIMIT 1
Then, a query for each getattr :
1/QueryOne: SELECT thastate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT thastate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT tsastate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT tsastate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT tssstate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT tssstate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT tsfstate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT tsfstate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT ensstate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT ensstate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT dvtstate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT dvtstate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT vvtstate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT vvtstate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT plustate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT plustate_id FROM validdata WHERE id =3D 78
1/COMMIT : auto
1/QueryOne: SELECT hrastate_id FROM validdata WHERE id =3D 78
1/QueryR : SELECT hrastate_id FROM validdata WHERE id =3D 78
What is the correct way of doing this in order to minimize the database
queries ?
Didrik
|