[sqlalchemy] Re: Bulk Lazy Loader for relationships
How do objects get registered into the aggressive loader? Does it happen automatically when they're initially loaded via query or cache? Ideally we wanted to group items together when they're loaded and do bulk lazy loading on that group rather than on all models in the session, but couldn't figure out a good way to accomplish that. Ex if we run users = session.query(User).limit(10).all() then we'd just want to do bulk lazy loading within the users from that query rather than all users that happen to be in the session, but it seems like we'd need to do some pretty invasive changes to SQLAlchemy to keep track of which models were initially loaded together. On Thursday, May 4, 2017 at 12:45:36 AM UTC+8, Jonathan Vanasco wrote: > > We have a somewhat tangential solution that was developed when building a > read-through cache that backs into SqlAlchemy. > > Instead of working on the Session, we register items for 'aggressive > loading' into a custom class instance that handles the coordination. > > Our system works like this: > > * objects are registered with the aggressive loader > * the aggressive loader is triggered: > ** the registered objects are recursively analyzed to find primary key > relationships that should be loaded > ** for each object type, the unloaded primary keys are loaded > ** some relationships are specified to run on a second pass > > Aside from the eagerloading advantages, this improved performance for a > few more reasons: > > * only the necessary data was requested/loaded. this eased the database > and wire traffic (i.e. at the db level a joined object is calculated once, > not once per relationship) > * the queries were easier on the DB, as simple "get by primary key" > requests - not wrapped joins. > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Bulk Lazy Loader for relationships
Interesting - so it will be possible in 1.2 to do more custom relationship loading in a "post load" hook? Thanks for the feedback! That definitely makes sense - I didn't fully understand all the logic in _emit_lazyload() initially so I wasn't sure what was OK to remove and what wasn't. I made a PR with those changes here: https://github.com/operator/sqlalchemy_bulk_lazy_loader/pull/1. One thing I still don't fully understand is the passive variable. What does passive mean, and is it set per-model or per-relationship? Thanks a lot Mike! On Wednesday, May 3, 2017 at 10:14:03 PM UTC+8, Mike Bayer wrote: > > > Related note, in 1.2 I'm adding a new mechanism for loading things which > is a "post load" hook, that is, a bunch of objects are loaded in a > query, and then afterwards, more loaders can run on a batch of completed > objects. The effect looks similar to how "subqueryload" works right > now, except "subqueryload" fires off within the initial population step > of the objects. The two kinds of loaders using this are the "select IN" > loading, which is like subqueryloading but more or less better in most > ways (also seems you're doing "select IN" here), and "select IN" loading > of subclass attributes on a joined inheritance mapping. > > The hook you have here would be a third kind of hook, a "lazyload that > works across the Session" hook. Definitely something new. I'm not > sure all of the logic that's been copied into _emit_lazyload() really > applies though; the top logic is all related to the specific object that > has triggered the load, like if its pending or not, if it had any query > option set up, the state.load_path, etc. You can't assume any of that > stuff applies to all the other states if you are going across the whole > result.It's probably better, since this is a very different kind of > loader, to make it just load for all the states in the same way without > looking at any of their options or things like that. > > > > On 05/03/2017 08:52 AM, David Chanin wrote: > > Ack, thanks Simon! That is definitely a bug :). I just pushed a fix. > > > > Thanks for the feedback! > > David > > > > On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote: > > > > Hi Everyone, > > > > We just open-sourced a custom lazy loader for SQLAlchemy that does > > bulk lazy loading of relations - essentially a lazy subqueryload. > > The idea is that whenever a relation is lazy-loaded on a model, the > > loader will look for all similar models in the session that haven't > > had that relation populated yet and will issue a single SQL query to > > populate them all in bulk. I'm really curious to hear any feedback > > you may have on this idea / implementation, pitfalls that we're > > overlooking, or ideas for improvement. The repo is at > > https://github.com/operator/sqlalchemy_bulk_lazy_loader > > <https://github.com/operator/sqlalchemy_bulk_lazy_loader>. Hope > it's > > useful to others as well. > > > > Thanks so much! > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com > > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. > > To post to this group, send email to sqlal...@googlegroups.com > > > <mailto:sqlal...@googlegroups.com >. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Bulk Lazy Loader for relationships
Ack, thanks Simon! That is definitely a bug :). I just pushed a fix. Thanks for the feedback! David On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote: > > Hi Everyone, > > We just open-sourced a custom lazy loader for SQLAlchemy that does bulk > lazy loading of relations - essentially a lazy subqueryload. The idea is > that whenever a relation is lazy-loaded on a model, the loader will look > for all similar models in the session that haven't had that relation > populated yet and will issue a single SQL query to populate them all in > bulk. I'm really curious to hear any feedback you may have on this idea / > implementation, pitfalls that we're overlooking, or ideas for improvement. > The repo is at https://github.com/operator/sqlalchemy_bulk_lazy_loader. > Hope it's useful to others as well. > > Thanks so much! > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Bulk Lazy Loader for relationships
Hi Everyone, We just open-sourced a custom lazy loader for SQLAlchemy that does bulk lazy loading of relations - essentially a lazy subqueryload. The idea is that whenever a relation is lazy-loaded on a model, the loader will look for all similar models in the session that haven't had that relation populated yet and will issue a single SQL query to populate them all in bulk. I'm really curious to hear any feedback you may have on this idea / implementation, pitfalls that we're overlooking, or ideas for improvement. The repo is at https://github.com/operator/sqlalchemy_bulk_lazy_loader. Hope it's useful to others as well. Thanks so much! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
