Hi,
I have an tree like object structue:
class Application(sqlobject.SQLObject):
jobs = sqlobject.MultipleJoin('Job')
class Job(sqlobject.SQLObject):
counterpartys = sqlobject.MultipleJoin('Counterparty')
class Counterparty(sqlobject.SQLObject):
name = sqlobject.StringCol()
There are two ways in which I can access the Counterpartys belonging to job 3
of application 0:
1) Traversing the tree:
app = Application.select()[0]
job = app.jobs[3]
[cp for cp in job.counterpartys]
2) Creating a query
[cp for cp in Counterparty.select(Job.q.id == 4 and Application.q.id == 1)]
In case 1) a SQL query for the job and then another one for each of the
counterpartys is created. Since there are many counterparties this yields a
huge number of SQL queries and things get very slow:
1/QueryAll: SELECT id FROM counterparty WHERE job_id = 4
1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM
counterparty WHERE id = 1
1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM
counterparty WHERE id = 2
1/QueryOne: SELECT name, rating, global_id, cif, job_id, nogas, sectors FROM
counterparty WHERE id = 3 ...
In case 2) this will yield a single SQL query
1/Select : SELECT counterparty.id, counterparty.name, counterparty.rating,
counterparty.global_id, counterparty.cif, counterparty.job_id,
counterparty.nogas, counterparty.sectors FROM application, counterparty WHERE
(application.id = 1)
I would like to use the syntax in 1), since the goal of using SQLObject is an
abstraction away from SQL statements towards objects, but with the performance
of 2).
Is there a way to achieve this, or an explanation why SQLObject cannot
optimize this?
Any help is appreciated.
Thank you! Yours,
Michael Gauckler
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
