Why is loading SQLAlchemy objects via the ORM 5-8x slower than rows via a raw MySQLdb cursor?

by

Here is the SQLAlchemy version of your MySQL script that performs in four seconds, compared to three for MySQLdb:

from sqlalchemy import Integer, Column, create_engine, MetaData, Table
import datetime

metadata = MetaData()

foo = Table(
    'foo', metadata,
    Column('id', Integer, primary_key=True),
    Column('a', Integer(), nullable=False),
    Column('b', Integer(), nullable=False),
    Column('c', Integer(), nullable=False),
)


class Foo(object):
    def __init__(self, a, b, c):
        self.a = a
        self.b = b
        self.c = c

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)
start = datetime.datetime.now()

with engine.connect() as conn:
    foos = [
        Foo(row['a'], row['b'], row['c'])
        for row in
        conn.execute(foo.select().limit(1000000)).fetchall()
    ]


print "total time: ", datetime.datetime.now() - start

runtime:

total time:  0:00:04.706010

Here is a script that uses the ORM to load object rows fully; by avoiding the creation of a fixed list with all 1M objects at once using yield per, this runs in 13 seconds with SQLAlchemy master (18 seconds with rel 0.9):

import time
from sqlalchemy import Integer, Column, create_engine, Table
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Foo(Base):
    __table__ = Table(
        'foo', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('a', Integer(), nullable=False),
        Column('b', Integer(), nullable=False),
        Column('c', Integer(), nullable=False),
    )


engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)

sess = Session(engine)

now = time.time()

# avoid using all() so that we don't have the overhead of building
# a large list of full objects in memory
for obj in sess.query(Foo).yield_per(100).limit(1000000):
    pass

print("Total time: %d" % (time.time() - now))

We can then split the difference between these two approaches, and load just individual columns with the ORM:

for obj in sess.query(Foo.id, Foo.a, Foo.b, Foo.c).yield_per(100).limit(1000000):
    pass

The above again runs in 4 seconds.

The comparison of SQLAlchemy Core is the more apt comparison to a raw MySQLdb cursor. If you use the ORM but query for individual columns, it’s about four seconds in most recent versions.

At the ORM level, the speed issues are because creating objects in Python is slow, and the SQLAlchemy ORM applies a large amount of bookkeeping to these objects as it fetches them, which is necessary in order for it to fulfill its usage contract, including unit of work, identity map, eager loading, collections, etc.

To speed up the query dramatically, fetch individual columns instead of full objects. See the techniques at
http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm which describe this.

For your comparison to PeeWee, PW is a much simpler system with a lot less features, including that it doesn’t do anything with identity maps. Even with PeeWee, about as simple of an ORM as is feasible, it still takes 15 seconds, which is evidence that cPython is really really slow compared to the raw MySQLdb fetch which is in straight C.

For comparison to Java, the Java VM is way way way faster than cPython. Hibernate is ridiculously complicated, yet the Java VM is extremely fast due to the JIT and even all that complexity ends up running faster. If you want to compare Python to Java, use Pypy.

Related Contents:

  1. Bulk insert with SQLAlchemy ORM
  2. SQLAlchemy: Scan huge tables using ORM?
  3. SQLAlchemy: engine, connection and session difference
  4. ImportError: No module named MySQLdb
  5. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  6. Efficiently updating database using SQLAlchemy ORM
  7. SQLAlchemy – Getting a list of tables
  8. flask-sqlalchemy or sqlalchemy
  9. Multiple columns index when using the declarative ORM extension of sqlalchemy
  10. flake8 complains on boolean comparison “==” in filter clause
  11. memory-efficient built-in SqlAlchemy iterator/generator?
  12. SQLAlchemy: how to filter date field?
  13. How to use NOT IN clause in sqlalchemy ORM query
  14. Connecting postgresql with sqlalchemy
  15. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  16. SQL Alchemy ORM returning a single column, how to avoid common post processing
  17. How to build a flask application around an already existing database?
  18. Avoiding “MySQL server has gone away” on infrequently used Python / Flask server with SQLAlchemy
  19. scoped_session(sessionmaker()) or plain sessionmaker() in sqlalchemy?
  20. How to create only one table with SQLAlchemy?
  21. SQLAlchemy: How do you delete multiple rows without querying
  22. SQLAlchemy “default” vs “server_default” performance
  23. SQLAlchemy ManyToMany secondary table with additional fields
  24. How to delete rows from a table using an SQLAlchemy query without ORM?
  25. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  26. How to update sqlalchemy orm object by a python dict
  27. CASE WHEN with ORM (SQLalchemy)
  28. SQLAlchemy ON DUPLICATE KEY UPDATE
  29. Bulk update in SQLAlchemy Core using WHERE
  30. About refreshing objects in sqlalchemy session
  31. How to create db in MySQL with SQLAlchemy?
  32. Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style)
  33. Speed comparison with Project Euler: C vs Python vs Erlang vs Haskell
  34. Using OR in SQLAlchemy
  35. Fastest way to get the first object from a queryset in django?
  36. Creating an empty list in Python
  37. How to move a model between two Django apps (Django 1.7)
  38. Fast way to copy dictionary in Python
  39. Select NULL Values in SQLAlchemy
  40. Can one use the Django database layer outside of Django?
  41. AttributeError while querying: Neither ‘InstrumentedAttribute’ object nor ‘Comparator’ has an attribute
  42. ImportError: No module named sqlalchemy
  43. How can I insert NULL data into MySQL database with Python?
  44. List of all unique characters in a string?
  45. How can I make a for-loop pyramid more concise in Python? [duplicate]
  46. Using a Python dict for a SQL INSERT statement
  47. Executing “SELECT … WHERE … IN …” using MySQLdb
  48. Bulk insert a Pandas DataFrame using SQLAlchemy
  49. How to know time spent on each test when using unittest?
  50. SQLAlchemy: a better way for update with declarative?

Leave a Comment