Re: [sqlalchemy] IN() Relationship Eager Loading Strategy

[mike bayer]

On 03/15/2017 01:12 AM, Paul Brown wrote:

tl;dr: I'm looking for an elegant way to eager load related rows using
separate IN() queries, because MySQL is "Using temporary; Using
filesort" when I try subquery eager loading.


My first attempt used joined eager loading, but that was causing
performance issues on my queries with nested relationships. This is
because joined eager loading uses a LEFT OUTER JOIN which loads the full
set of columns represented by the parents on each row of the results.
The "What Kind of Loading to Use?"
part
of the docs explains the issue pretty well.


Next, I tried switching to subquery eager loading to fix the problem.
But, MySQL 5.6 says "Using temporary; Using filesort" when I run an
explain on the query. It did seem to make the query run ~20% faster, but
I'm worried about the load that would put on my database. Example:
https://gist.github.com/pawl/bde2737c4d217b468eba1107a03fbcb5


Next, I was hoping I could get SQLAlchemy to use its own internal
storage in the session to find related rows if I already queried for
them. However, this only seems to work one way by default. It will still
run queries (if you’re not doing eager loading) if you access relations
from the “one” side of a one to many relationship. Example:
https://gist.github.com/pawl/abc0e536219144e569c728c8590b0d39


At this point, I'm really wishing there was an eager loading strategy
that queries for the related rows using IN(). So, I read the "Creating
Custom Load Rules"
section
of the docs and came up with an example that added the related rows with
"set_committed_value" after I queried for them separately:
https://gist.github.com/pawl/df5ba8923d9929dd1f4fc4e683eced40



So I'd like to someday add support for the IN() thing, I had a feeling 
that sqlalchemy-utils supplied this but I'm not seeing it in their docs.


There's a recipe for "roll your own eager loading" at 
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisjointEagerLoading 
which is written for subquery eager loading before it was added.The 
IN version is similar, you just need to grab the related primary keys as 
you get the primary rows.


However I would note that others are not having this problem with "using 
temporary; using filesort" with MySQL 5.6.There should be ways to 
set up your schema so that an efficient plan is used.   I'd want to look 
into fixing it on that end first if possible.






Is there a more elegant way to do this? And, has adding an IN() eager
loading strategy to SQLAlchemy been considered?

--
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 - 
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] IN() Relationship Eager Loading Strategy

[Paul Brown]

tl;dr: I'm looking for an elegant way to eager load related rows using 
separate IN() queries, because MySQL is "Using temporary; Using filesort" 
when I try subquery eager loading.

My first attempt used joined eager loading, but that was causing 
performance issues on my queries with nested relationships. This is because 
joined eager loading uses a LEFT OUTER JOIN which loads the full set of 
columns represented by the parents on each row of the results. The "What 
Kind of Loading to Use?" 

 
part of the docs explains the issue pretty well.

Next, I tried switching to subquery eager loading to fix the problem. But, 
MySQL 5.6 says "Using temporary; Using filesort" when I run an explain on 
the query. It did seem to make the query run ~20% faster, but I'm worried 
about the load that would put on my database. Example: 
https://gist.github.com/pawl/bde2737c4d217b468eba1107a03fbcb5

Next, I was hoping I could get SQLAlchemy to use its own internal storage 
in the session to find related rows if I already queried for them. However, 
this only seems to work one way by default. It will still run queries (if 
you’re not doing eager loading) if you access relations from the “one” side 
of a one to many relationship. Example: 
https://gist.github.com/pawl/abc0e536219144e569c728c8590b0d39

At this point, I'm really wishing there was an eager loading strategy that 
queries for the related rows using IN(). So, I read the "Creating Custom 
Load Rules" 

 
section of the docs and came up with an example that added the related rows 
with "set_committed_value" after I queried for them separately: 
https://gist.github.com/pawl/df5ba8923d9929dd1f4fc4e683eced40

Is there a more elegant way to do this? And, has adding an IN() eager 
loading strategy to SQLAlchemy been considered?

-- 
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.