Perf/eager collection bulk extend by ollz272 · Pull Request #13416 · sqlalchemy/sqlalchemy

ollz272 · Jul 6, 2026

Description

What changed

Every eagerly-loaded child paid two wasted Python frames: the generated
instrumentation wrapper (append/add) plus its __set no-op shell, even
though with _sa_initiator=False the wrapper's entire body reduces to the
raw built-in operation.

At class-instrumentation time, the generated append/add wrapper is
tagged _sa_trivial when — and only when — it wraps exactly
list.append / set.add. _set_collection_attributes then computes two
class attributes: _sa_bulk_appender (list.extend / set.update /
None) and _sa_raw_appender (list.append / set.add / None).
CollectionAdapter.append_multiple_without_event (the choke point for
selectin / subquery / immediate / lazy collection loads via
set_committed_value) uses the bound bulk op when present.
CollectionAdapter.append_without_event (the per-row path of the joined
collection eager loader) uses the raw single op.
Strict gate: any customization keeps the per-item instrumented loop —
subclass append/add overrides, @collection.appender,
@collection.internally_instrumented, dict-based collections
(attribute_keyed_dict / KeyFuncDict), ext.orderinglist.OrderingList
(assigns positions per append), and set classes with custom
__contains__.

Performance (`bench_hydration.py`, 1000 repeats, min / median vs main)

case	min Δ	median Δ
selectin_m2m	−4.9%	−4.6%
selectin_nested	−3.8%	−3.4%
selectin_o2m	−3.2%	−3.2%
selectin_o2m_few_big	−3.3%	−2.3%
subquery_o2m	−1.6%	−1.7%
joined_o2m (raw-append path)	−1.2%	−1.2%
controls (m2o / plain / scalar joined)	~0%	~0%

Win pattern matches the mechanism exactly: concentrated where collection
appends occur; controls flat.

Testing

16 gate unit tests (TrivialAppenderGateTest), 9 loader integration tests
incl. observability tests proving the instrumented appender is NOT called
on the fast path (BulkAppendLoaderTest), and 6 gating-armor tests proving
each excluded collection type still gets per-item appender calls
(BulkAppendGatingTest). 680 tests green across collection + eager-loader +
orderinglist + associationproxy suites.

Benchmark harness: bench_hydration.py

"""Benchmark ORM hydration performance across many loading patterns.

Usage:
    python bench_hydration.py                  # run all cases
    python bench_hydration.py --case NAME ...  # run selected cases
    python bench_hydration.py --profile NAME   # cProfile one case
    python bench_hydration.py --json out.json  # dump results as JSON

Each case pre-populates an in-memory SQLite database, warms the query
cache, then times repeated query executions in fresh sessions so that
object hydration (not compilation) dominates.
"""

from __future__ import annotations

import argparse
import cProfile
import gc
import json
import pstats
import statistics
import sys
import time

from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import joinedload
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload
from sqlalchemy.orm import Session
from sqlalchemy.orm import subqueryload


class Base(DeclarativeBase):
    pass


class Parent(Base):
    __tablename__ = "parent"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))
    a: Mapped[int]
    b: Mapped[int]
    c: Mapped[str] = mapped_column(String(64))
    children: Mapped[list["Child"]] = relationship(back_populates="parent")
    tags: Mapped[list["Tag"]] = relationship(secondary="parent_tag")


class Child(Base):
    __tablename__ = "child"

    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int] = mapped_column(ForeignKey("parent.id"))
    name: Mapped[str] = mapped_column(String(64))
    x: Mapped[int]
    y: Mapped[str] = mapped_column(String(64))
    parent: Mapped[Parent] = relationship(back_populates="children")
    grandchildren: Mapped[list["GrandChild"]] = relationship()


class GrandChild(Base):
    __tablename__ = "grandchild"

    id: Mapped[int] = mapped_column(primary_key=True)
    child_id: Mapped[int] = mapped_column(ForeignKey("child.id"))
    name: Mapped[str] = mapped_column(String(64))


class Tag(Base):
    __tablename__ = "tag"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(64))


class ParentTag(Base):
    __tablename__ = "parent_tag"

    parent_id: Mapped[int] = mapped_column(
        ForeignKey("parent.id"), primary_key=True
    )
    tag_id: Mapped[int] = mapped_column(ForeignKey("tag.id"), primary_key=True)


class Wide(Base):
    __tablename__ = "wide"

    id: Mapped[int] = mapped_column(primary_key=True)
    # 24 additional columns
    for _i in range(24):
        exec(f"col{_i}: Mapped[int]")
    del _i


def build_db(num_parents, children_per_parent, grandchildren_per_child,
             tags_per_parent, num_wide):
    engine = create_engine("sqlite://")
    Base.metadata.create_all(engine)
    with Session(engine) as sess:
        tags = [Tag(name=f"tag{i}") for i in range(50)]
        sess.add_all(tags)
        sess.flush()
        pid = cid = gid = 1
        for p in range(num_parents):
            parent = Parent(
                id=pid, name=f"parent{p}", a=p, b=p * 2, c=f"c{p}"
            )
            sess.add(parent)
            for t in range(tags_per_parent):
                sess.add(
                    ParentTag(
                        parent_id=pid, tag_id=(p + t) % len(tags) + 1
                    )
                )
            for ch in range(children_per_parent):
                child = Child(
                    id=cid,
                    parent_id=pid,
                    name=f"child{cid}",
                    x=cid,
                    y=f"y{cid}",
                )
                sess.add(child)
                for g in range(grandchildren_per_child):
                    sess.add(
                        GrandChild(
                            id=gid, child_id=cid, name=f"gc{gid}"
                        )
                    )
                    gid += 1
                cid += 1
            pid += 1
        for w in range(num_wide):
            sess.add(
                Wide(id=w + 1, **{f"col{i}": w + i for i in range(24)})
            )
        sess.commit()
    return engine


CASES = {}


def case(name, **db_kw):
    def decorate(fn):
        CASES[name] = (fn, db_kw)
        return fn

    return decorate


DEFAULT_DB = dict(
    num_parents=500,
    children_per_parent=10,
    grandchildren_per_child=0,
    tags_per_parent=0,
    num_wide=0,
)


@case("plain_small")
def plain_small(engine):
    """500 Parent rows, 5 columns, no relationships."""
    with Session(engine) as sess:
        objs = sess.scalars(select(Parent)).all()
    assert len(objs) == 500
    return objs


@case("plain_wide", num_wide=2000)
def plain_wide(engine):
    """2000 rows x 25 columns."""
    with Session(engine) as sess:
        objs = sess.scalars(select(Wide)).all()
    assert len(objs) == 2000
    return objs


@case("selectin_o2m")
def selectin_o2m(engine):
    """500 parents, selectinload 10 children each."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(selectinload(Parent.children))
        ).all()
    assert len(objs) == 500 and len(objs[0].children) == 10
    return objs

@case(
    "selectin_o2m_few_big",
    num_parents=20,
    children_per_parent=500,
)
def selectin_o2m_few_big(engine):
    """20 parents x 500 children: collection-heavy."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(selectinload(Parent.children))
        ).all()
    assert len(objs) == 20 and len(objs[0].children) == 500
    return objs


@case("selectin_m2o", num_parents=200, children_per_parent=25)
def selectin_m2o(engine):
    """5000 children, selectinload of many-to-one parent."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Child).options(selectinload(Child.parent))
        ).all()
    assert len(objs) == 5000 and objs[0].parent is not None
    return objs


@case(
    "selectin_nested",
    num_parents=50,
    children_per_parent=10,
    grandchildren_per_child=10,
)
def selectin_nested(engine):
    """50 x 10 x 10 two-level selectinload chain."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(
                selectinload(Parent.children).selectinload(
                    Child.grandchildren
                )
            )
        ).all()
    assert len(objs) == 50 and len(objs[0].children[0].grandchildren) == 10
    return objs


@case("selectin_m2m", num_parents=500, tags_per_parent=10)
def selectin_m2m(engine):
    """500 parents, selectinload 10 many-to-many tags each."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(selectinload(Parent.tags))
        ).all()
    assert len(objs) == 500 and len(objs[0].tags) == 10
    return objs


@case("joined_o2m")
def joined_o2m(engine):
    """joinedload comparison for the selectin_o2m case."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(joinedload(Parent.children))
        ).unique().all()
    assert len(objs) == 500 and len(objs[0].children) == 10
    return objs


@case("joined_m2o", num_parents=200, children_per_parent=25)
def joined_m2o(engine):
    """joinedload of a many-to-one (scalar) parent: the common joined case.

    Unlike joined_o2m there is no collection row explosion, so .unique()
    is not required and each row drives a distinct (new) Child instance.
    """
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Child).options(joinedload(Child.parent))
        ).all()
    assert len(objs) == 5000 and objs[0].parent is not None
    return objs


@case("subquery_o2m")
def subquery_o2m(engine):
    """subqueryload comparison for the selectin_o2m case."""
    with Session(engine) as sess:
        objs = sess.scalars(
            select(Parent).options(subqueryload(Parent.children))
        ).unique().all()
    assert len(objs) == 500 and len(objs[0].children) == 10
    return objs


def run_case(name, repeats, profile=False):
    fn, db_kw = CASES[name]
    engine = build_db(**{**DEFAULT_DB, **db_kw})
    # warm up: compile cache, sqlite page cache
    fn(engine)
    fn(engine)
    if profile:
        prof = cProfile.Profile()
        prof.enable()
        for _ in range(repeats):
            fn(engine)
        prof.disable()
        stats = pstats.Stats(prof)
        stats.sort_stats("cumulative").print_stats(45)
        engine.dispose()
        return None
    times = []
    for _ in range(repeats):
        gc.collect()
        t0 = time.perf_counter()
        fn(engine)
        times.append(time.perf_counter() - t0)
    engine.dispose()
    return times


def compute_stats(times):
    """Return summary statistics for a list of timings, in milliseconds."""
    ms = sorted(t * 1000 for t in times)
    if len(ms) == 1:
        centiles = ms * 99
    else:
        # percentile indexes per the "inclusive" method, interpolating
        # between the two nearest sample points
        centiles = statistics.quantiles(ms, n=100, method="inclusive")
    return {
        "min": ms[0],
        "max": ms[-1],
        "mean": statistics.fmean(ms),
        "median": statistics.median(ms),
        "stdev": statistics.stdev(ms) if len(ms) > 1 else 0.0,
        "p90": centiles[89],
        "p95": centiles[94],
        "p99": centiles[98],
    }


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--case", action="append", choices=sorted(CASES))
    parser.add_argument("--repeats", type=int, default=20)
    parser.add_argument("--profile", choices=sorted(CASES))
    parser.add_argument("--json")
    args = parser.parse_args()

    if args.profile:
        run_case(args.profile, args.repeats, profile=True)
        return

    names = args.case or sorted(CASES)
    results = {}
    stat_names = ("min", "max", "mean", "median", "stdev", "p90", "p95", "p99")
    print(
        f"{'case':<24}"
        + "".join(f"{stat:>10}" for stat in stat_names)
        + "   (ms)"
    )
    for name in names:
        times = run_case(name, args.repeats)
        stats = compute_stats(times)
        results[name] = {
            **{f"{stat}_ms": stats[stat] for stat in stat_names},
            "times": times,
        }
        print(
            f"{name:<24}"
            + "".join(f"{stats[stat]:>10.3f}" for stat in stat_names)
        )
    if args.json:
        with open(args.json, "w") as f:
            json.dump(results, f, indent=2)
        print(f"\nwrote {args.json}", file=sys.stderr)


if __name__ == "__main__":
    main()

Checklist

This pull request is:

A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed
A short code fix
- please include the issue number, and create an issue if none exists, which
  must include a complete example of the issue. one line code fixes without an
  issue and demonstration will not be accepted.
- Please include: Fixes: #<issue number> in the commit message
- please include tests. one line code fixes without tests will not be accepted.
A new feature implementation
- please include the issue number, and create an issue if none exists, which must
  include a complete example of how the feature would look.
- Please include: Fixes: #<issue number> in the commit message
- please include tests.

Have a nice day!

…ation time

…_event

…/eager-collection-bulk-extend

setting _sa_trivial on the generated append/add wrappers follows the existing wrapper._sa_instrumented idiom; mypy needs the same type: ignore[attr-defined] annotation

ashm-dev

1. _sa_trivial bypass via functools.wraps

The getattr(appender, "_sa_trivial", None) check is fooled if a custom override uses @functools.wraps(InstrumentedList.append): wraps copies the wrapped function's __dict__, leaking both _sa_instrumented and _sa_trivial into the subclass override.

Result: the subclass incorrectly gets _sa_bulk_appender = list.extend and its override is silently skipped on eager loads, while event-ful appends still call it. Split behavior within one class. Same for the set variant; wrapt/decorator-style wrappers leak the same way.

Fix: keep a module-level WeakSet of the wrappers generated in _list_decorators/_set_decorators and check membership in _set_collection_attributes, instead of relying on a copyable function attribute. Add a regression test with a wraps-decorated override.

2. Vacuous asserts in test_ordering_list_positions_on_eager_load

The fixture writes position = 0, 1, 2, and with the default reorder_on_append=False OrderingList assigns nothing for non-NULL positions anyway. The test does fail under a forced-bulk mutant, but only via the internal is_none(_sa_bulk_appender) assert; the behavioral asserts (positions/data) pass regardless, so they verify nothing.

Fix: write position=None in the fixture so the positions assert becomes a real behavioral check.

3. Untested lazy loader

The PR description claims fast-path support for lazy loading, but tests only cover selectin/subquery/joined.

Fix: add lazy to the loader test matrix.

4. Process

Missing linked issue. The changelog file cannot be named without a ticket number.
Missing changelog entry under doc/build/changelog/unreleased_21/.
Clean up the history: rebase to remove the merge commit.

5. Adjustments

"Exactly equivalent" overstates the set case: the old wrapper hashes each value twice (value not in self + set.add), set.update hashes once. State is identical, side-effect counts are not. Soften the comments.
_sa_bulk_appender next to bulk_appender() is confusing. Rename the former to _sa_raw_bulk_append or add cross-references.
Benchmarks: the exact invocation isn't given (script defaults to 20 repeats, table says 1000), environment details are missing, and in-memory SQLite inflates the percentages vs real databases. Please include the command line and the --json output.

The core mechanism otherwise checks out: with _sa_initiator=False the fast path is equivalent for untouched list/set, and ordinary customizations (subclass overrides, @collection.appender, internally_instrumented, KeyFuncDict, OrderingList) are gated correctly.

zzzeek · Jul 11, 2026

            self._refuse_empty()
-        self._data()._sa_appender(item, _sa_initiator=False)
+        data = self._data()
+        raw_appender = data._sa_raw_appender


is it possible to name the "preferred" appender on the collection up front rather than testing each time?

ollz272 added 6 commits July 6, 2026 09:07

perf(orm): detect trivial built-in collection appenders at instrument…

cf1b926

…ation time

perf(orm): bulk-extend trivial collections in append_multiple_without…

4a798bc

…_event

test(orm): strict gating coverage for trivial collection bulk append

62a413b

perf(orm): raw single-append fast path in append_without_event

2b8b611

chore: Merge branch 'main' of github.com:ollz272/sqlalchemy into perf…

45e6612

…/eager-collection-bulk-extend

fix: annotate function-attribute tags for mypy

496d92c

setting _sa_trivial on the generated append/add wrappers follows the existing wrapper._sa_instrumented idiom; mypy needs the same type: ignore[attr-defined] annotation

ashm-dev suggested changes Jul 6, 2026

View reviewed changes

zzzeek reviewed Jul 11, 2026

View reviewed changes

fix: pr comments

3a9ecdd

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

Perf/eager collection bulk extend - #13416

Perf/eager collection bulk extend#13416
ollz272 wants to merge 7 commits into
sqlalchemy:mainsqlalchemy/sqlalchemy:mainfrom
ollz272:perf/eager-collection-bulk-extendollz272/sqlalchemy:perf/eager-collection-bulk-extendCopy head branch name to clipboard

ollz272 commented Jul 6, 2026

Uh oh!

ashm-dev left a comment

Uh oh!

zzzeek Jul 11, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Search code, repositories, users, issues, pull requests...

Uh oh!

Uh oh!

Conversation

ollz272 commented Jul 6, 2026

Description

What changed

Performance (bench_hydration.py, 1000 repeats, min / median vs main)

Testing

Checklist

Uh oh!

ashm-dev left a comment

Choose a reason for hiding this comment

Uh oh!

zzzeek Jul 11, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Performance (`bench_hydration.py`, 1000 repeats, min / median vs main)