On Mar 14, 2012, at 5:23 AM, Thibaud Roussillat wrote: > Hi, > > I work with sqlalchemy v 0.4 (not possible to migrate to new versions > quickly) and I try to improve my application's performance because I > have to play with big sets of data contained in two tables. > > I have table A and table B with a one-to-many relation (table B > contain a foreign key to table A). A sqlalchemy relation is define > between the two tables and I can access B items associated to the > current row of A with .itemsB. > > A pseudo-algorithm code of what I'm doing : > > for itemA in itemsA: > for itemB in itemA.itemsB > # Play with itemB > > This work perfectly, however, each time I call itemA.itemsB, a new > request is done to the database and the performance are not good. > > Knowing the set of primary key of table A I'm interested in, is there > anyway to preload table B data in memory to avoid to request the > database each time I ask itemA.itemsB ?
the usual way to do this, even in 0.4, is to use eagerload() (called joinedload() today): session.query(A).options(eagerload(A.itemsB)).filter(...).all() that will run a join against A to B and load everything in one query. Modern versions of SQLA have another approach called subqueryload() which emits a different kind of query that may be more efficient in some cases, but joinedload() (called eagerload() in 0.4) should get you most of the way there. -- 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.
