On Oct 7, 2012, at 2:17 AM, me wrote:
> using python 2.7, sqla 0.7.9, postgresql 9.1
>
> i've setup joined table inheritance and wanted an eager loaded relationship
> from one of the derived objects to another derived object. when i do that i
> get what seems to be a really inefficient query.
>
> here is the minimal setup:
>
> http://pastebin.com/yS7gDfju
>
> and this is the output:
>
> SELECT
> ...
> FROM root
> JOIN type2 ON root.id = type2.id
> LEFT OUTER JOIN (SELECT root.id AS root_id, root.type AS root_type, type1.id
> AS type1_id, type1.field1 AS type1_field1
> FROM root JOIN type1 ON root.id = type1.id)
> AS anon_1 ON type2.type1_id = anon_1.type1_id
>
> what i expected was:
>
> SELECT
> ...
> FROM root
> JOIN type2 ON root.id = type2.id
> LEFT OUTER JOIN root as root_1 ON root_1.id = type2.type1_id
> LEFT OUTER JOIN type1 ON type1.id = root_1.id
>
> which runs *much* faster for the data sets i've tried.
>
> is there a way to force the relationship to generate the 2nd form?
there's not, and the best this would ever do if SQLA were to have a particular
set of improvements that are fairly involved, would be this form:
select .. from root JOIN type2 ON <onclause> LEFT OUTER JOIN (root JOIN
type1 ON <onclause>) ON <onclause>
that is, the target eager join would be parenthesized without the SELECT
subquery part of it. Most databases with the exception of SQLite and older
MySQL versions support parenthesization of this form.
The grouping is necessary as it is very difficult to "de-parenthsize" the joins
in the way you describe without changing the meaning of complex queries. Just
removing the SELECT has shown to be a complex task. Supporting auto-rendering
of a JOIN b LEFT OUTER JOIN (c JOIN d) within joined eager loading is related
to ticket #2369, so I've added this variant as a use case.
Normally, if this were just an explicit query.join(), the way to get exactly
the query you want would be to use the Table objects to construct exactly the
join you want, as introduced at
http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html#advanced-control-of-which-tables-are-queried
. This can be combined with contains_eager() to provide joined eager loading
against a specific query. But in this case the eager loading wouldn't be
automatic.
> or am i doing something i shouldn't?
I don't often use "lazy='joined'" as part of a relationship(). I'd only use
it on many-to-one, as is the case here, but also with many-to-ones I'll often
leave those as "lazy", if the case is such that I'm loading lots of Type2s that
refer to a small set of Type1s, I'd only get as many SELECT statements as there
are Type1 identities referred to in the whole set being loaded.
Another trick that I haven't tried but might work is to make the relationship
to Root, and then modify the primaryjoin to also specify the polymorphic
identity of "type1":
relationship("Root", primaryjoin="and_(Type2.type1_id == Root.id,
Root.type=='TYPE1')")
I haven't tried that but it might work.
Finally, if performance is critical, I'd be shying away from excessive use of
joined inheritance in the first place. I actually built a datamodel that was
PG and based a large and complex series of models on a shared
joined-inheritance base, and it was this kind of AjoinB->AjoinC thing all the
way, and eventually you could kind of feel how much work you were making the
database do just for a little bit of purity, it probably wasn't worth it. The
way it ended up working was that we made extensive usage of relationship
caching using the Beaker caching example in the docs (which will become
dogpile.cache in 0.8), and then turned off most eager loading. The
many-to-ones were typically pulling from a limited set of entities that was
just 100% cached within a few minutes of the app first starting up.
Can't quite give you the answer you want, just a bunch of ways to almost get
there; hope one of them works out.
>
> thanks
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/0laXtaxdh8UJ.
> 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.
--
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.
