CODEWITHSUNDEEP
pythonsqlalchemy
hl037_
hl037_

Reputation: 3867

How to skip ORM overhead in result?

I have an ORM sqlalchemy model, I need to construct a query (that is easier to construct with ORM classes), but it takes a huge amount of time. When I execute the same query directly as SQL to the database, it is quite fast.

Using the profile function provided in SQLAlchemy doc https://docs.sqlalchemy.org/en/latest/faq/performance.html I've found it is indeed the ORM adding a huge overhead.

However, the query result is used in several piece of code, so it would be a headache to change everywhere.

The query looks like this : 

q = ( session.query(T1, T2)
  .select_from(sub_query)
  .join(T1, sub_query.c.id == T1.id)
  .join(T2, sub_query.c.id==T2.id)
  .filter(T1.user == 1)
  .order_by(T1.group, T1.position)
)

The rows are not modified, they are just read using the T1 and T2 attributes. (I mean, I don't need to get a T1 and a T2 object, just something like a raw object with attributes matching T1's and T2's column names)

Is there a way to speed it up (removing ORM overhead) so that I can access them with the same syntax e.g.

for t1, t2 in q.all():
  print(t1.x, t2.x)

?

I already tried sqlalchemy.orm.Bundle, but I get something like "Bundle is not selectable" (certainly due to the join)

Upvotes: 1

Views: 1147

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

If you wish to continue using SQLAlchemy, but avoid the overhead of the ORM in this particular situation, you can use Bundle as you've tried:

from sqlalchemy import inspect
from sqlalchemy.orm import Bundle

def to_bundle(cls, label=None): 
    return Bundle(label or cls.__name__,
                  *[c.class_attribute for c in inspect(cls).column_attrs])

q = ( session.query(to_bundle(T1), to_bundle(T2))
  .select_from(sub_query)
  .join(T1, sub_query.c.id == T1.id)
  .join(T2, sub_query.c.id==T2.id)
  .filter(T1.user == 1)
  .order_by(T1.group, T1.position)
)

You should not pass the bundles to Query.join(), but the mapped classes. The bundles will pick up the columns from the results of the joins. If you do pass the bundle you get the error:

AttributeError: 'Bundle' object has no attribute 'selectable'

Upvotes: 2

Related Questions