Suppose we have original generated query like that:
SELECT company.x AS company_x, ...
FROM company
LEFT OUTER JOIN acc ON acc.id = company.acc
LEFT OUTER JOIN usercomp_links ON company.id = usercomp_links.pid
LEFT OUTER JOIN usergro_links ON acc.id = usergro_links.pid
WHERE usergro_links.eid = %s OR usercomp_links.eid = %s
And if we add .options(subqueryload(Company.childs))
to this, we will get:
SELECT company.x AS company_x, ..., anon_1.company_id AS anon_1_company_id
FROM (
SELECT company.id AS company_id
FROM company
LEFT OUTER JOIN acc ON acc.id = company.acc
LEFT OUTER JOIN usercomp_links ON company.id = usercomp_links.pid
LEFT OUTER JOIN usergro_links ON acc.id = usergro_links.pid
WHERE usergro_links.eid = %s OR usercomp_links.eid = %s) AS anon_1
INNER JOIN acel_links AS acel_links_1 ON anon_1.company_id = acel_links_1.eid
INNER JOIN company ON company.id = acel_links_1.pid ORDER BY anon_1.company_id
And this is sloooow. If I will get company ids from first query, and load all child companies by hands, it will be blazingly fast in compare to what we get in this case.
I have read documentation, looked at the code, but dont see if I can tell sqlalchemy just get ids from results of first query and load childs in separate, comparatively simple query. I dont rely on this sample - I had more more harder situations, when sqlalchemy just cant load constructed query. And why to do all this work from first query one more time?
So anyone know how to eager load without auto constructed "join from join in join" style?
Lazy loading refers to objects are returned from a query without the related objects loaded at first. When the given collection or reference is first accessed on a particular object, an additional SELECT statement is emitted such that the requested collection is loaded.
Deprecated since version 2.0: The features of “connectionless” and “implicit” execution in SQLAlchemy are deprecated and will be removed in version 2.0. See “Implicit” and “Connectionless” execution, “bound metadata” removed for background.
Execution Slowness this would indicate that the database is taking a long time to start returning results, and it means your query should be optimized, either by adding indexes or restructuring the query and/or underlying schema.
Python Flask and SQLAlchemy ORM All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.
Update: the "select in" strategy is now implemented in SQLAlchemy (since v 1.2): see Select IN loading in the documentation.
TLDR:
I think the joinedload
strategy should be used where possible, as it is more efficient than other strategies, including the suggested in the question strategy to load related data using the "IN" statement.
The "IN" strategy can be easily enough implemented "outside" of the SQLAlchemy (see the code below) and probably it should not be complex to implement it as a new loading strategy (as logically it is similar to existing subqueryload
strategy).
Full version:
I started with a simple experiment to see the queries produced by different strategies
The full source code of the experiment is on Github.
My models look this way:
class Author(ModelBase):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True, nullable=False)
name = Column(String(255))
class Book(ModelBase):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
name = Column(String)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship(
'Author', backref=backref('books'))
Now, the tests, first is lazy loading:
books = session.query(Book).all()
print books[0].author.name
session.commit()
Output (cleaned up):
-------------Lazy--------------
sqlalchemy.engine.base.Engine:
SELECT
books.id AS books_id, books.name AS books_name, books.author_id AS books_author_id
FROM books
SELECT
authors.id AS authors_id, authors.name AS authors_name
FROM authors
WHERE authors.id = ?
INFO:sqlalchemy.engine.base.Engine:(1,)
author1
As expected, lazy loading runs one query to fetch books and one query each time we access an author.
Subquery loading:
books = session.query(Book).options(subqueryload(Book.author)).all()
print books[0].author.name
session.commit()
-------------Subquery----------
SELECT
books.id AS books_id, books.name AS books_name, books.author_id AS books_author_id
FROM books
SELECT
authors.id AS authors_id, authors.name AS authors_name,
anon_1.books_author_id AS anon_1_books_author_id
FROM (
SELECT DISTINCT books.author_id AS books_author_id
FROM books) AS anon_1
JOIN authors
ON authors.id = anon_1.books_author_id
ORDER BY anon_1.books_author_id
author1
For the subquery, we have two queries, first fetches books and another fetches authors using the subquery.
Joined loading:
books = session.query(Book).options(joinedload(Book.author)).all()
print books[0].author.name
session.commit()
-------------Joined------------
SELECT
books.id AS books_id, books.name AS books_name,
books.author_id AS books_author_id,
authors_1.id AS authors_1_id, authors_1.name AS authors_1_name
FROM books
LEFT OUTER JOIN authors AS authors_1 ON authors_1.id = books.author_id
author1
The joined strategy runs just one query to fetch both books and authors.
Immediate loading:
books = session.query(Book).options(immediateload(Book.author)).all()
print books[0].author.name
session.commit()
-------------Immediate---------
SELECT
books.id AS books_id, books.name AS books_name, books.author_id AS books_author_id
FROM books
SELECT
authors.id AS authors_id, authors.name AS authors_name
FROM authors
WHERE authors.id = ?
INFO:sqlalchemy.engine.base.Engine:(1,)
SELECT authors.id AS authors_id, authors.name AS authors_name
FROM authors
WHERE authors.id = ?
INFO:sqlalchemy.engine.base.Engine:(2,)
author1
And the immediate
strategy loads books with the first query and then, when we try to access the relation, fetches all the related data with separate query for each related record.
It looks that "joinedload()" should be most efficient in most cases (amd more efficient than "IN" strategy) - we just get all the data with single query.
Now, lets try to implement the IN strategy outside of SQL alchemy:
print '-------------IN----------------'
books = session.query(Book).all()
ids = set()
for b in books:
ids.add(b.author_id)
authors = session.query(Author).filter(Author.id.in_(ids)).all()
print books[0].author.name
print books[1].author.name
print books[2].author.name
print books[3].author.name
Output:
-------------IN----------------
SELECT
books.id AS books_id, books.name AS books_name, books.author_id AS books_author_id
FROM books
SELECT authors.id AS authors_id, authors.name AS authors_name
FROM authors
WHERE authors.id IN (?, ?)
INFO:sqlalchemy.engine.base.Engine:(1, 2)
author1
author1
author2
author2
As we can see, it runs two queries and then we can access all the authors.
Note that we don't join authors to books explicitly, but it still works when we try to access authors through the books, since SQLAlchemy finds author records in the internal identity map and doesn't run additional DB queries.
The "IN" strategy code similar to above can be generalized into the function which can be used with any model / relation. And probably, the "IN" strategy should be relatively easy to implement as a new SQLAlchemy strategy, it is similar to the existing subqueryloading
- it also should run the second query to get the related data.
http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#sqlalchemy.orm.selectinload
It was added to sqlalchemy, so now you can just use selectinload
strategy.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With