[Sqlalchemy-tickets] Issue #4347: performance issue when fetching mapped columns individually in conjunction with he entity (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4347: performance issue when fetching mapped columns individually in conjunction with he entity
https://bitbucket.org/zzzeek/sqlalchemy/issues/4347/performance-issue-when-fetching-mapped

Michael Bayer:

this is due to AnnotatedColumn resulting in an expensive comparison when we are doing the dictionary lookup in result row:

```
#!python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base


import cProfile
from io import StringIO
import pstats
import contextlib


@contextlib.contextmanager
def profiled(name):
    pr = cProfile.Profile()
    pr.enable()
    yield
    pr.disable()
    ps = pstats.Stats(pr).sort_stats('cumulative')
    print("Test %s total calls: %d" % (name, ps.total_calls))


Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    data = Column(String)

e = create_engine("sqlite://")
Base.metadata.create_all(e)

s = Session(e)

s.add_all([A(data='asdf') for i in range(1)])
s.commit()


def no_bundle(s):
    for i in range(100):
        s.query(
            A
        ).select_from(A).all()


def no_entity_wo_annotations(s):
    for i in range(100):
        s.query(
            A.__table__.c.data
        ).select_from(A).all()


def no_entity_w_annotations(s):
    for i in range(100):
        s.query(
            A.data
        ).select_from(A).all()


def no_bundle_wo_annotations(s):
    for i in range(100):
        s.query(
            A.__table__.c.data, A
        ).select_from(A).all()


def no_bundle_w_annotations(s):
    for i in range(100):
        s.query(
            A.data, A
        ).select_from(A).all()


def bundle_wo_annotation(s):
    for i in range(100):
        s.query(
            Bundle("ASdf", A.__table__.c.data), A
        ).select_from(A).all()


def bundle_w_annotation(s):
    for i in range(100):
        s.query(
            Bundle("ASdf", A.data), A
        ).select_from(A).all()

with profiled("no bundle"):
    no_bundle(Session(e))

with profiled("no entity wo annotations"):
    no_entity_wo_annotations(Session(e))

with profiled("no entity w annotations"):
    no_entity_w_annotations(Session(e))

with profiled("no bundle wo annotations"):
    no_bundle_wo_annotations(Session(e))

with profiled("no bundle w annotations"):
    no_bundle_w_annotations(Session(e))

with profiled("bundle but using Core column"):
    bundle_wo_annotation(Session(e))

with profiled("bundle against annotated A.data"):
    bundle_w_annotation(Session(e))
```

result:


```
#!python

Test no bundle total calls: 35822
Test no entity wo annotations total calls: 28078
Test no entity w annotations total calls: 28785
Test no bundle wo annotations total calls: 40673
Test no bundle w annotations total calls: 49073
Test bundle but using Core column total calls: 44471
Test bundle against annotated A.data total calls: 54267


```

something weird is going on.  when we have the plain column, plus the full entity, the plain column is getting pulled into the resultproxy key lookup, then when the ORM looks in the key lookup by column, the annotated column is expensive inside the dictionary due to `__eq__` occurring.  This actually goes all the way into default_comparator and is very expensive.

not really sure how to fix this.