[sqlalchemy] Re: Many to many optimization
On Apr 12, 2007, at 11:38 PM, Kaali wrote: I actually tried to use query.instances, but it behaved quite oddly. I didn't debug or even echo the SQL calls yet, but it made accessing those instances very slow. The actual instances call was quick, but when accessing the objects from the resulting list it slowed down to crawl. make sure you are using the contains_eager() option to indicate the relationships that are eagerly loading. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
Thanks for the answers.
I implemented message loading with find_members() kind of method, as
shown in the documentation link you gave, and it got twice as fast.
But it's still nowhere near the speed without the ORM. Makes me a bit
sad, as i really liked the ORM system. Maybe if i remove any automatic
relations and manually get them it would be faster.
Or maybe i should use ORM normally, but work without it on
bottlenecks.
--
K
On Apr 2, 8:20 pm, Michael Bayer [EMAIL PROTECTED] wrote:
sqlalchemy relationships are currently only fully loading - meaning
you cant directly filter on an instances collection attribute.
however you can construct your own query based on the relationship
and use that, and theres plenty of tools to make that easy.
such as, if you set up a bi-directional relationship:
class A(object):pass
class B(object):pass
mapper(B, b_table)
mapper(A, a_table, properties={
b_collection : relation(A, secondary=a_to_b_table,
backref=a_collection)
})
if you load an instance of A:
mya = session.query(A).select(a.id=7)
you can query the Bs on an A via the backref:
result = session.query(B).filter_by(a_collection=mya).filter
(A.c.somecriterion=='foo').list()
theres also some patterns for dealing with large collections at:
http://www.sqlalchemy.org/docs/
adv_datamapping.html#advdatamapping_properties_working
On Apr 2, 2007, at 7:44 AM, Kaali wrote:
Can i use ORM many-to-many relations and filter SQL -side? If i can't,
can you give me an example on how i should use many-to-many relations
with filtering in SQLAlchemy?
--
K
On Apr 2, 1:36 pm, svilen [EMAIL PROTECTED] wrote:
After getting the results, i will filter them with Python filters,
as i don't know how to filter many-to-many queries directly.
Should i somehow make custom queries that handles many-to-many
relationships etc. or is there something else i'm missing that
makes the system slow? I have ran the bench with MySQL and
PostgreSQL engines, the result is the same. When running with a
profiler, at least ~90% of the time is taken by SQLAlchemy.
one general suggestion, try to move the filtering on the sql side;
thus u'll have less data transferred then instantiated then
filtered - which is probably eating most of the time.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to [EMAIL PROTECTED]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
On Apr 12, 2007, at 3:30 AM, Kaali wrote: Thanks for the answers. I implemented message loading with find_members() kind of method, as shown in the documentation link you gave, and it got twice as fast. But it's still nowhere near the speed without the ORM. i get the impression youre trying to do a partial eager load. any query that you can execute by itself can be fed into the ORM and turned into object-mapped results, including the (partial, incomplete, whatever) fulfillment of whatever relationships you like, using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
while on the same subject, how do i copy one object's relatives to another object without loading them all? user1.addresses = user2.addreses does not work, it makes them share the same InstrList user1.addresses = user2.addreses[:] does work, but fires a full query (maybe with obj-instantiation) This is in the case of implicit association, using secondary=table On Thursday 12 April 2007 18:17:11 Michael Bayer wrote: On Apr 12, 2007, at 3:30 AM, Kaali wrote: Thanks for the answers. I implemented message loading with find_members() kind of method, as shown in the documentation link you gave, and it got twice as fast. But it's still nowhere near the speed without the ORM. i get the impression youre trying to do a partial eager load. any query that you can execute by itself can be fed into the ORM and turned into object-mapped results, including the (partial, incomplete, whatever) fulfillment of whatever relationships you like, using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
sorry, ignore this, started a new thread --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
I actually tried to use query.instances, but it behaved quite oddly. I didn't debug or even echo the SQL calls yet, but it made accessing those instances very slow. The actual instances call was quick, but when accessing the objects from the resulting list it slowed down to crawl. I will recreate that situtation later and see where to slowdown is. I'll also give some more implementation details so that you can see if there just a stupid mistake i have made that makes SA slow. -- K On Apr 12, 6:17 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 12, 2007, at 3:30 AM, Kaali wrote: Thanks for the answers. I implemented message loading with find_members() kind of method, as shown in the documentation link you gave, and it got twice as fast. But it's still nowhere near the speed without the ORM. i get the impression youre trying to do a partial eager load. any query that you can execute by itself can be fed into the ORM and turned into object-mapped results, including the (partial, incomplete, whatever) fulfillment of whatever relationships you like, using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
After getting the results, i will filter them with Python filters, as i don't know how to filter many-to-many queries directly. Should i somehow make custom queries that handles many-to-many relationships etc. or is there something else i'm missing that makes the system slow? I have ran the bench with MySQL and PostgreSQL engines, the result is the same. When running with a profiler, at least ~90% of the time is taken by SQLAlchemy. one general suggestion, try to move the filtering on the sql side; thus u'll have less data transferred then instantiated then filtered - which is probably eating most of the time. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Many to many optimization
sqlalchemy relationships are currently only fully loading - meaning
you cant directly filter on an instances collection attribute.
however you can construct your own query based on the relationship
and use that, and theres plenty of tools to make that easy.
such as, if you set up a bi-directional relationship:
class A(object):pass
class B(object):pass
mapper(B, b_table)
mapper(A, a_table, properties={
b_collection : relation(A, secondary=a_to_b_table,
backref=a_collection)
})
if you load an instance of A:
mya = session.query(A).select(a.id=7)
you can query the Bs on an A via the backref:
result = session.query(B).filter_by(a_collection=mya).filter
(A.c.somecriterion=='foo').list()
theres also some patterns for dealing with large collections at:
http://www.sqlalchemy.org/docs/
adv_datamapping.html#advdatamapping_properties_working
On Apr 2, 2007, at 7:44 AM, Kaali wrote:
Can i use ORM many-to-many relations and filter SQL -side? If i can't,
can you give me an example on how i should use many-to-many relations
with filtering in SQLAlchemy?
--
K
On Apr 2, 1:36 pm, svilen [EMAIL PROTECTED] wrote:
After getting the results, i will filter them with Python filters,
as i don't know how to filter many-to-many queries directly.
Should i somehow make custom queries that handles many-to-many
relationships etc. or is there something else i'm missing that
makes the system slow? I have ran the bench with MySQL and
PostgreSQL engines, the result is the same. When running with a
profiler, at least ~90% of the time is taken by SQLAlchemy.
one general suggestion, try to move the filtering on the sql side;
thus u'll have less data transferred then instantiated then
filtered - which is probably eating most of the time.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
