Peformance on attribute access

[exrich]

I have found that accessing object attributes on SQLAlchemy is very slow. For example

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

Base = declarative_base()


class A(Base):

    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)


class B:

    def __init__(self, id):

        self.id = id


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

s = Session(e)

a1 = A(id=1)
b1 = B(1)

%timeit a1.id
298 ns ± 5.14 ns per loop (mean ± std. dev. of 7 runs, 1000000 loops each)

%timeit b1.id
31.1 ns ± 0.477 ns per loop (mean ± std. dev. of 7 runs, 10000000 loops each)

approx 10 times slower to lookup the value of an attribute. The difference is much less if I do this:

%timeit a1.__dict__['id']
72.3 ns ± 15.8 ns per loop (mean ± std. dev. of 7 runs, 10000000 loops each)

In my actual code I'm getting an object from the db then making it transient to perform some intensive computations but this attribute access is really slowing everything down. Should I be doing something else to remove the object from sqlalchemy whilst I do these computations?

Thx

[zzzeek]

if you are doing a CPU bound operation with "a.id" and doing lots of access, you should assign it to a variable up front: a_id = a.id. SQLAlchemy mapped attributes use Python descriptors for access. this is the code in minimal form:

class Getter:
    def __init__(self, key):
        self.key = key

    def __get__(self, instance, owner):
        if instance is None:
            return self

        dict_ = instance.__dict__
        if self.key in dict_:
            return dict_[self.key]
        else:
            print("lazy fetch logic occurs here")
            raise AttributeError()


class C:
    def __init__(self, id):
        self.id = id

    id = Getter("id")

it's the "key in dict" that is slowing it down. this check is trying to optimize the case when the item is not in the dictionary, which is whenever it has to load the attrifbute, and we want to avoid the expense of catching an exception. but maybe this should be reversed.

[zzzeek]

reversing it isn't helping that much for some reason

diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index c7c2425010..e2f4ddb997 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -270,10 +270,12 @@ class InstrumentedAttribute(QueryableAttribute):
             return self
 
         dict_ = instance_dict(instance)
-        if self._supports_population and self.key in dict_:
-            return dict_[self.key]
-        else:
-            return self.impl.get(instance_state(instance), dict_)
+        if self._supports_population:
+            try:
+                return dict_[self.key]
+            except KeyError:
+                pass
+        return self.impl.get(instance_state(instance), dict_)
 
 
 def create_proxied_attribute(descriptor):

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

Base = declarative_base()


class A(Base):

    __tablename__ = "a"
    id = Column(Integer, primary_key=True)


class B:
    def __init__(self, id):

        self.id = id


class Getter:
    def __init__(self, key):
        self.key = key

    def __get__(self, instance, owner):
        if instance is None:
            return self

        dict_ = instance.__dict__
        try:
            return dict_[self.key]
        except KeyError:
            print("lazy fetch logic occurs here")
            raise AttributeError()


class C:
    def __init__(self, id):
        self.id = id

    id = Getter("id")


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

s = Session(e)

a1 = A(id=1)
b1 = B(1)
c1 = C(1)

print(timeit.timeit(lambda: a1.id))
print(timeit.timeit(lambda: b1.id))
print(timeit.timeit(lambda: c1.id))

old way:

0.7903756340965629
0.17934147699270397
0.18463402893394232

new way:

0.6784084030659869
0.18671153800096363
0.18339014996308833

[zzzeek]

theres some things to play with if you feel like working on this

[exrich]

Thx for the info and ideas.

I've profiled the get method in line_profiler:

%lprun -f orm.attributes.InstrumentedAttribute.__get__ a2.id
Timer unit: 1e-07 s
Total time: 6.6e-06 s
File: C:\Users\richc\Anaconda3\lib\site-packages\sqlalchemy\orm\attributes.py
Function: __get__ at line 269
Line #      Hits         Time  Per Hit   % Time  Line Contents
==============================================================
   269                                               def __get__(self, instance, owner):
   270         1         14.0     14.0     21.2          if instance is None:
   271                                                       return self
   272                                           
   273         1         21.0     21.0     31.8          dict_ = instance_dict(instance)
   274         1         19.0     19.0     28.8          if self._supports_population:
   275         1          7.0      7.0     10.6              if self.key in dict_:
   276         1          5.0      5.0      7.6                  return dict_[self.key]
   277                                                   else:
   278                                                       return self.impl.get(instance_state(instance), dict_)

It appears the instance_dict, supports_population methods cause a lot of the slow down. I've decided to use the a1.dict['id'] work around for the time being. I'm not really sure what the purpose of these is but I guess it would be a lot of work to change.

[zzzeek]

I can factor out the "self._supports_population" by assigning a pre-configured __get__ method on startup. but now I remember where Python has a problem here. If you add a __set__ to your descriptor, that makes it nearly three times slower than without:

import timeit


class Getter:
    def __init__(self, key):
        self.key = key

    def __get__(self, instance, owner):
        if instance is None:
            return self

        dict_ = instance.__dict__
        try:
            return dict_[self.key]
        except KeyError:
            print("lazy fetch logic occurs here")
            raise AttributeError()


class GetterWithSetter:
    def __init__(self, key):
        self.key = key

    def __get__(self, instance, owner):
        if instance is None:
            return self

        dict_ = instance.__dict__
        try:
            return dict_[self.key]
        except KeyError:
            print("lazy fetch logic occurs here")
            raise AttributeError()

    def __set__(self, instance, value):
        instance.__dict__[self.key] = value


class C:
    def __init__(self, id):
        self.id = id

    id = Getter("id")
    id_w_set = GetterWithSetter("id")


c1 = C(1)

print(timeit.timeit(lambda: c1.id))
print(timeit.timeit(lambda: c1.id_w_set))

output:

0.1709344880655408
0.4276733750011772

That's most of the speed loss. Python really works against us here, sorry.

[zzzeek]

I tried this rather awkward inlining:

diff --git a/lib/sqlalchemy/orm/attributes.py b/lib/sqlalchemy/orm/attributes.py
index 879b88a928..514aec5b94 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -265,16 +265,41 @@ class InstrumentedAttribute(QueryableAttribute):
     def __delete__(self, instance):
         self.impl.delete(instance_state(instance), instance_dict(instance))
 
-    def __get__(self, instance, owner):
-        if instance is None:
-            return self
+    def _get_supports_pop_expect_populated(self, instance, owner):
+        dict_ = instance_dict(instance)
+        try:
+            return dict_[self.key]
+        except KeyError:
+            return self.impl.get(instance_state(instance), dict_)
 
+    def _get_supports_pop_expect_unpopulated(self, instance, owner):
         dict_ = instance_dict(instance)
-        if self._supports_population and self.key in dict_:
+        if self.key in dict_:
             return dict_[self.key]
         else:
             return self.impl.get(instance_state(instance), dict_)
 
+    def _get_impl_only(self, instance, owner):
+        dict_ = instance_dict(instance)
+
+        return self.impl.get(instance_state(instance), dict_)
+
+    @util.memoized_property
+    def _get_impl(self):
+        if self._supports_population:
+            if self.impl.uses_objects:
+                return self._get_supports_pop_expect_unpopulated
+            else:
+                return self._get_supports_pop_expect_populated
+        else:
+            return self._get_impl_only
+
+    def __get__(self, instance, owner):
+        if instance is None:
+            return self
+        else:
+            return self._get_impl(instance, owner)
+
 
 def create_proxied_attribute(descriptor):
     """Create an QueryableAttribute / user descriptor hybrid.

and it still is coming up at .78 for the above test. changing instance_dict() (which is an operator.attrgetter, supposed to be super fast) to instance.dict saves us like .1 on the test (which I think is like a million iterations with timeit?) so this is all splitting hairs, can't really be improved. if you have something so intensively cpu bound that this stuff makes a difference you should assign to a variable, as you would in any case.

[Klutch27]

This thread was super helpful for a problem I was trying to figure out around optimization. Thanks to both of you for the thorough explanations!

[FahdCodes]

Surprisingly this is the only thread in the entire Internet that is talking about this issue in sqlalchemy.

I'm facing the same issue. I have a list of 4000 objects fetched from the database and accessing the attributes of 4000 objects and assigning it to a variable (as suggested by zzzeek) takes about 11 seconds which is simply a no no for a user facing application.

Is there any other workaround this? Like telling sqlalchemy to fetch the entire object instead of the instance of the object (sqlalchemy.orm.state.InstanceState object at 0x7f7596c61a30>). The docs suggest to pull the cols instead of object rows (https://docs.sqlalchemy.org/en/20/faq/performance.html#result-fetching-slowness-orm), but my app is almost ready and do not want to do modify my database queries and the related logic at this point.

One silver lining I've noticed is that further up the chain (I'm using flask) marshmallow is able to access the object attributes in the blink of an eye. I don't know how it does that.

Really appreciate any suggestions. Thanks!

[zzzeek]

Surprisingly this is the only thread in the entire Internet that is talking about this issue in sqlalchemy.

I'm facing the same issue. I have a list of 4000 objects fetched from the database and accessing the attributes of 4000 objects and assigning it to a variable (as suggested by zzzeek) takes about 11 seconds which is simply a no no for a user facing application.

use direct row access, not objects:

for row in session.execute(select(MyClass.id, MyClass.foo)):
print(f"id: {row.id} foo: {row.foo}")

rows are implemented in cython and have a very direct access pattern that isn't doing things like lazy loading and tracking mutations.

Is there any other workaround this? Like telling sqlalchemy to fetch the entire object instead of the instance of the object (sqlalchemy.orm.state.InstanceState object at 0x7f7596c61a30>).

I'm not following what that would mean. the "entire" object and the "instance" of the object are the same thing.

The docs suggest to pull the cols instead of object rows (https://docs.sqlalchemy.org/en/20/faq/performance.html#result-fetching-slowness-orm), but my app is almost ready and do not want to do modify my database queries and the related logic at this point.

identify the hotspots and change only the queries in those hotspots.

One silver lining I've noticed is that further up the chain (I'm using flask) marshmallow is able to access the object attributes in the blink of an eye. I don't know how it does that.

those objects likely dont use python descriptors

[FahdCodes]

Thank you so much! That did the trick - Direct row access instead of objects.

Sorry I wasn't clear with my comment. I was using db.session.scalars(db.select(MyClass).all() and it was the attributes of the resultant ScalarResult that the app was taking forever to access. Based on your feedback I'm now using [row for row in db.session.execute(db.select(MyClass.id, MyClass.foo).all()]. Sqlalchemy is able to access the attributes of the row object much faster than the ScalarResult object. yayy!!

Finally, just to clarify on my comment on marshmallow, I'm actually throwing the ScalarResult retrieved with db.session.scalars to a Marshmallow schema before rendering it on the webpage (using flask as the back-end), something like this -

class SomeMarshmallowSchema(Schema):
    id = fields.Str()                          #marshmallow schema accessing the attributes of the ScalarResult
    foo = fields.Str()


@blp.route("/endpoint")
class SomeName(MethodView):
    @blp.response(200, SomeMarshmallowSchema(many=True))    #many=True handles lists of objects
    def get(self):
        kwargs = request.args
        return asyncio.run(myfunc(**kwargs))                      #returns the ScalarResults fetched using db.session.scalars

This code is rendering the output really fast. Must be some trick in Flask.