Morning Martin:
I could be wrong, but I think what you're looking for is lazy='joined'
rather than lazy='subquery'.
When I change the following, I see one query per showDatabase() call
rather than two.
class Men(Human):
....
myChildren = relationship('Child', secondary=link_table, lazy='joined')
class Woman(Human):
....
myChildren = relationship('Child', secondary=link_table, lazy='joined')
Here's how I think of it, with examples from:
http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html?highlight=subqueryload#using-loader-strategies-lazy-loading-eager-loading
* Case 1 (1 + N queries):
# set children to load lazily
session.query(Parent).options(lazyload('children')).all()
* Case 2 (1 query):
# set children to load eagerly with a join
session.query(Parent).options(joinedload('children')).all()
* Case 3 (2 queries):
# set children to load eagerly with a second statement
session.query(Parent).options(subqueryload('children')).all()
The subqueryload() and lazy='subquery' options emit an *additional*
SQL statement for each collection requested, but at least it's not N
queries (one for each child).
Thanks for including isolated code to easily reproduce the question.
Cheers,
--diana
On Wed, Nov 21, 2012 at 3:25 AM, Martin84 <steko...@googlemail.com> wrote:
> Hi,
>
> I use SQLalchemy 0.7.9 with SQLite and have a performance issue.
>
> I have a simple database with joined table inheritance and some
> relationships.
>
> In short: I have a base class Human, and 3 subclasses (Men, Woman and
> Child), and I have a class House.
>
> The house class have a many to many relationship to the human class (a house
> have residents, and every human have houses).
>
> And there are two more relationship, the Woman and the Men class have a many
> to many relationship to the Child class (a men/woman can have many
> children).
>
>
> Here you can see my model , query code: http://pastebin.com/mcum0c7Q
>
> The issue is: if I load a house from the database with house =
> session.query(House).first() and then access the residents of this house
> with "house.residents",
> and iterate over the residents and access the children of every resident
> then sqlalchemy emits a new sqlquery on every access:
> for resident in house.residentes:
> print resident.myChildren # emits new sql-query
>
> This is very bad for my performance, what I need is a solution, that load
> with a single "session.query(House)-Command" all residents AND all children
> of the residents at once!
> Is this possible?
> For the many to many relationship between the Women/Men and the Child class
> is use lazy='subquery', but sqlalchemy ignore this! Why?
>
> I hope someone could help me.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
