-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty
Sent: 23 September 2009 15:48
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] unexpected chained relations and
append behaviour
Hello everyone,
I have a realy simple model for you to consider:
1 car has n wheels
car.wheels is a relation from cars to wheels
wheel.car is a backref to cars
1 car has n parts
car.parts is a relation from car to parts
I just wondered why my app was really getting slow, turned on SA debug
mode, and saw that
my_new_doorknob = model.Part(doorknob)
wheel.car.parts.append(my_new_door_knob)
is downloading the entire parts table WHERE parts.car == car.id
(that is around 20.000 entries) just so that it can append my new
doorknob to that relation.
Furthermore I noticed a similar behaviour when doing
something like this:
amount_of_parts = len(car.parts)
Instead of sending a COUNT to the database, it populates the entire
car.parts relation (around 20.000 entries) just to get the count. Of
course I could avoid using relations, and just use my __init__
functions, or setting:
my_new_doorknob = model.Part(doorknob)
my_new_doorknob.car_id = car.id
DBSession.append(my_new_doorknob)
But then I could as well just write literal SQL if I cant use the R
part of ORM...
Has anyone observed similar behaviour or is this a feature and
intended to work like this?
Greetings, Tom
Yes, this is exactly how it is intended to work. You may like to read
http://www.sqlalchemy.org/docs/05/mappers.html#working-with-large-collec
tions for hints on how to improve performance. In particular, making
your car.parts property a 'dynamic' relation rather than the default
will prevent SA from loading the entire collection unless you
specifically ask it to.
However, the len(car.parts) line won't work. SA deliberately doesn't
implement the __len__ method for Query objects because it is called
implicitly by python in a number of situations, and running a
potentially slow query when you aren't expecting it is a bad idea.
Instead you would use car.parts.count().
Hope that helps,
Simon
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
