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 = validdata.select(AND(validdata.q.stationID == station.id,
validdata.q.mtime == row.mtime))[0]
# compare and update states
for state in states:
# check state
current_state = getattr(vdata, state)
...
The tables are the following :
class validdata(SQLObject):
mtime = DateTimeCol(notNone=True)
station = ForeignKey('stations',notNone=True)
tha = DecimalCol( size=3,precision=1,default=None)
tha_state = ForeignKey('validstates', notNone=True, default=1)
tsa = DecimalCol( size=3,precision=1,default=None)
tsa_state = ForeignKey('validstates', notNone=True, default=1)
...
class validstates(SQLObject):
name = StringCol(alternateID=True, notNone=True, unique=True)
description = 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 = validdata.select(AND(validdata.q.stationID == station.id,
validdata.q.mtime == 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.
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 = 1)
AND (validdata.mtime = '2006-01-04 05:00:00')) LIMIT 1
Then, a query for each getattr :
1/QueryOne: SELECT thastate_id FROM validdata WHERE id = 78
1/QueryR : SELECT thastate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT tsastate_id FROM validdata WHERE id = 78
1/QueryR : SELECT tsastate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT tssstate_id FROM validdata WHERE id = 78
1/QueryR : SELECT tssstate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT tsfstate_id FROM validdata WHERE id = 78
1/QueryR : SELECT tsfstate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT ensstate_id FROM validdata WHERE id = 78
1/QueryR : SELECT ensstate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT dvtstate_id FROM validdata WHERE id = 78
1/QueryR : SELECT dvtstate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT vvtstate_id FROM validdata WHERE id = 78
1/QueryR : SELECT vvtstate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT plustate_id FROM validdata WHERE id = 78
1/QueryR : SELECT plustate_id FROM validdata WHERE id = 78
1/COMMIT : auto
1/QueryOne: SELECT hrastate_id FROM validdata WHERE id = 78
1/QueryR : SELECT hrastate_id FROM validdata WHERE id = 78
What is the correct way of doing this in order to minimize the database
queries ?
Didrik
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 [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
