> have I missed something ?
> I've not seen the reply on the list.
The reply went to my private address, see below.
-------- Forwarded Message --------
From: Luke Opperman <[EMAIL PROTECTED]>
To: Michael Gauckler <[EMAIL PROTECTED]>
Subject: Re: [SQLObject] Reuducing the number of SQL Queries for
MultipleJoins
Date: Mon, 26 Jun 2006 19:14:02 -0500
I haven't looked closely at it, and it seems your debug sql statements don't
quite match the code you've posted, but i believe if you switch the
counterpartys join to be a SQLMultipleJoin it will match what you want. That
will use a SelectResults for the join instead of the old join logic.
Luke
Quoting Michael Gauckler <[EMAIL PROTECTED]>:
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
