Query Optimization¶

SQLAlchemy¶

1. joinedload()¶

Problem solved: N+1 query problem for ForeignKey / One-to-One

Loads related objects using a JOIN in the same query.

from sqlalchemy.orm import joinedload

session.query(Book).options(
    joinedload(Book.author)
).all()

Generated SQL Query¶

SELECT 
    books.id AS books_id, 
    books.title AS books_title, 
    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;

Equivalent to Django select_related()

Use when:

• Relation is many-to-one
• Dataset size is moderate

2. selectinload()¶

Problem solved: N+1 query problem for collections

Loads relations using IN queries.

from sqlalchemy.orm import selectinload

session.query(Author).options(
    selectinload(Author.books)
).all()

Generated SQL Queries¶

Query 1: Load the Parents

SELECT authors.id AS authors_id, authors.name AS authors_name 
FROM authors;

Query 2: Load the Children (The "IN" Query)

SELECT books.id AS books_id, books.title AS books_title, books.author_id AS books_author_id 
FROM books 
WHERE books.author_id IN (1, 2, 3);

Equivalent to Django prefetch_related()

Use when:

• One-to-many
• Many-to-many
• Large result sets

3. subqueryload()¶

Problem solved: Alternative solution for N+1 queries

Uses a subquery to fetch related data.

from sqlalchemy.orm import subqueryload

session.query(Author).options(
    subqueryload(Author.books)
)

Use when:

• selectinload performs poorly
• Database optimizer prefers subqueries

4. lazyload()¶

Problem solved: Prevent unnecessary eager loading

Forces a relationship to load only when accessed.

from sqlalchemy.orm import lazyload

session.query(Book).options(
    lazyload(Book.author)
)

Use when:

• Relation is rarely used
• You want a minimal initial query

5. noload()¶

Problem solved: Completely disable relationship loading

from sqlalchemy.orm import noload

session.query(Book).options(
    noload(Book.author)
)

Use when:

• Relationship is never needed
• Optimizing heavy queries

6. contains_eager()¶

Problem solved: Avoid duplicate joins when manually writing joins

from sqlalchemy.orm import contains_eager

session.query(Book).join(Book.author).options(
    contains_eager(Book.author)
)

Use when:

• You already wrote a JOIN
• You want SQLAlchemy to populate the relationship

7. load_only()¶

Problem solved: Over-fetching columns

Loads only specific columns.

from sqlalchemy.orm import load_only

session.query(User).options(
    load_only(User.id, User.name)
)

Improves:

• Network performance
• Memory usage

8. defer()¶

Problem solved: Delay loading large columns

from sqlalchemy.orm import defer

session.query(Document).options(
    defer(Document.large_text)
)

The large column loads only when accessed.

9. undefer()¶

Problem solved: Force loading of a deferred column

from sqlalchemy.orm import undefer

session.query(Document).options(
    undefer(Document.large_text)
)

10. with_entities()¶

Problem solved: Avoid loading full ORM objects

session.query(User).with_entities(
    User.id, User.email
)

Returns tuples instead of full models.

Use when only a few fields are needed.

11. exists()¶

Problem solved: Efficient existence checks

from sqlalchemy import exists

session.query(
    exists().where(User.email == email)
).scalar()

Equivalent to Django .exists().

12. yield_per()¶

Problem solved: Memory issues with large queries

Streams results in batches.

session.query(User).yield_per(1000)

Use when processing millions of rows.

13. bulk_save_objects()¶

Problem solved: Slow bulk inserts

session.bulk_save_objects(users)

Much faster than normal ORM inserts.

14. bulk_insert_mappings()¶

Problem solved: Very large insert performance

session.bulk_insert_mappings(User, data)

Bypasses ORM object creation.

15. execution_options(stream_results=True)¶

Problem solved: Memory usage for huge queries

query = session.query(User).execution_options(stream_results=True)

16. aliased()¶

Problem solved: Self-joins or multiple references to the same table

from sqlalchemy.orm import aliased

manager = aliased(User)

session.query(User).join(manager, User.manager_id == manager.id)

17. with_for_update()¶

Problem solved: Race conditions / concurrent updates

session.query(Account).with_for_update()

Locks rows during the transaction.

18. scalars()¶

Problem solved: Simplify result extraction

session.scalars(select(User))

Returns model objects directly.

19. select()¶

Problem solved: Modern SQLAlchemy querying (2.0 style)

from sqlalchemy import select

stmt = select(User).where(User.id == 1)
session.execute(stmt)

20. relationship(lazy="selectin")¶

Problem solved: Default N+1 prevention at the model level

books = relationship("Book", lazy="selectin")

Automatically prefetches related data.

Summary¶