Mati Skiva wrote:
> Here is the query produced by SQLAlchemy:
> SELECT * FROM object_markings LEFT OUTER JOIN (SELECT * FROM resources
> INNER JOIN files ON resources.id = files.resource_id) AS S ON
> object_markings.image_resource_id = S.id WHERE object_markings.id IN
> (<list of id's>)
>
> I made a tiny change: ObjectMarkingDB is related to FileDB rather than
> ResourceDB (what i wrote was a mistake)
that was my initial thought but you didn't illustrate that in your
mapping. in this case you should use contains_eager() to optimize such a
query. Because the FileDB represents a mapping to a join, and to "join"
to a "join" generically requires that it be wrapped inside a subquery.
Also in 0.6 you'll be able to turn that OUTER JOIN into an INNER.
>
> The query seems rather straightforward, however it is extremely slow,
> and that is not an exaggeration.
its likely you're on MySQL which has unacceptable performance regarding
subqueries. If I were you I'd use only single table inheritance on MySQL.
> My own method of performing this join was using temporary tables.
that sounds strange....
>
>
> How would you advice me on speeding up the performance of this query?
> session.query(ObjectMarkingDB).filter(ObjectMarkingDB.id.in_([om.id for
> om in markings])).options(eagerload("imageResource")).all()
use the individual Table objects in your query to specify only those rows
which you need. You only need load the primary key columns from each
table at the bare minimum, so you could for example join to FileDB's
parent table alone. then use contains_eager() to specify what eagerly
loaded collections are represented in the result set. Always think in
terms of the ideal SQL query first, then apply it to an ORM structure.
>
> Mati
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
