Michael Bayer wrote:
> On Nov 1, 2009, at 8:10 AM, matiskiva wrote:
>
>
>> The sql query to perform the eager loading is horrible, more horrible
>> than anything i can imagine.
>>
>
> This is certainly an exaggeration, and its always a good idea to
> please tell us on the list what output you're receiving, so that I
> don't have to guess what your problem is. Assuming ResourceDB is not
> a subclass of another joined-table, and no with_polymorphic() setting
> is used, the query should look like:
>
> select <columns> from object_markings_table left outer join
> resources_table on <onclause> where <filter criterion>
>
> If you are applying filtering criterion against resources_table,
> assuming that you can re-use the columns specified by the eagerload,
> that is a user error. See http://www.sqlalchemy.org/trac/wiki/
> FAQ
> #ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN
>
> for details. You'll get a cartesian product in such a case.
>
> if you apply LIMIT or OFFSET to your query, 0.5 will wrap the "eager"
> query into a subquery so that the LIMIT/OFFSET works properly in the
> case where the eagerload specifies a collection. 0.6 will skip this
> step if all eagerloads specify many-to-one. 0.6 also provides the
> option to use INNER JOIN instead of OUTER JOIN.
>
> Your configuration of foreign keys, primaryjoin, and uselist are all
> essentially the defaults of such a relation() and as long as there is
> a foreign key between object_markings_table and resources_table, are
> all unnecessary. So there is possibly something erroneous about your
> table /mapper configuration not illustrated here such that you were
> compelled to go down the route of defining everything explicitly,
> which also may be the source of an awkward query.
>
>
>
>> I managed to write my own sql statement that takes a fraction of the
>> time, the question is:
>> Lets say i have a collection of ResourceDB objects, how do i
>> internally connect them to the ObjectMarkingDB instances?
>>
>> Or in other words: how do i implement my own eager loading for this
>> specific purpse?
>>
>
> the contains_eager() option is used for this purpose:
> http://www.sqlalchemy.org/docs/05/mappers.html#routing-explicit-joins-statements-into-eagerly-loaded-collections
>
>
>> And in formallity, lets say i have a collection of ResourceDBs and i
>> collection of ObjectMarkingDBs. how do i:
>> for every ResourceDb in ResourceDBs: connect ResourceDB to relevant
>> ObjectMarkingDB
>>
>
> to piece together collections manually from already loaded objects,
> you can populate collections as though they were loaded from the
> database using attributes.set_commited_value:
> http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html#sqlalchemy.orm.attributes.set_committed_value
>
>
>
> >
>
> This mail was received via Mobileye Mail-SeCure system.
>
>
>
Hello,
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)
The query seems rather straightforward, however it is extremely slow,
and that is not an exaggeration.
My own method of performing this join was using temporary tables.
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()
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
-~----------~----~----~----~------~----~------~--~---
