I see significant slow down if I have 1000 items, and a couple of annotations for each one. That's a 1001 queries I need to make, so I want to avoid the for loop there - and just have 1 query that returns me the list of correct annotation objs.
I think this is basically the "give me the last order each user placed" problem, but w/ more complexity around the "last order" definition. http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html Cheers, Lars On May 30, 12:06 pm, Lance Edgar <[email protected]> wrote: > On 5/30/2010 7:36 AM, nospam wrote:> Thanks Lance. Would this lazily load > the annotations for each item? > > I'm trying to avoid "# of item" trips to the db, and also avoid > > loading all the annotations.for each item. I'm thinking I can do w/ > > some joins, and subquery()'s... > > This would load the annotations lazily in that they would not be fetched > along with each item initially, but they would be as soon as you > reference item.annotations in code. This behavior is default, but you > can explicitly state it in your mapper like so: > > orm.mapper( > Item, items, > properties = dict( > annotations = orm.relation(Annotation, > order_by=[annotations.c.creation_datetime.desc()], lazy='select'), > ), > ) > > More on the relationship mapping is > athttp://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.... > . > > However, it sounds like you need to know about the annotations within > your item loop anyway, so I'm not clear what you look to save yourself > in terms of "# of item trips to the db"? Just a suggestion, but I think > improving the speed of things might be easier later, and possibly even > unnecessary. Or it could be that I'm just not understanding what you're > after here. ;) > > Also remember you can pass echo=True when creating your engine, e.g.: > > my_engine = sa.create_engine('sqlite:///', echo=True) > > Then you'll see exactly what SQL is being issued to your database and in > what order, etc. > (http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html#...) > > Lance -- 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.
