CODEWITHSUNDEEP
pythonoracle-databasesqlalchemy
Muposat
Muposat

Reputation: 1506

SQLAlchemy performance of SELECT is 2x slower than a raw connection to Oracle

I am timing get_core and get_orm against get_raw with the following results:

get_raw got 10000 rows, time: 0:00:04.201974
get_core got 10000 rows, time: 0:00:08.048311
get_orm got 10000 rows, time: 0:00:08.768862

Full code:

from datetime import datetime
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import select
Base = declarative_base()

class TbPerformanceTest(Base):
    __tablename__ = 'tb_performance_test'
    i = Column(Integer, primary_key=True)
    s = Column(String(100))

def get_raw(engine):
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute('select * from tb_performance_test')
    return cursor.fetchall()

def get_core(engine):
    sel = select([TbPerformanceTest.__table__])
    return list(engine.execute(sel))

def get_orm(engine):
    session = sessionmaker(bind=engine)()
    return list(session.query(TbPerformanceTest))

def main():
    engine = create_engine('engine connection string')
    TbPerformanceTest.__table__.create(engine)
    session = sessionmaker(bind=engine)()
    for i in range(10000):
        session.add(TbPerformanceTest(i=i, s=str(i)))
    session.commit()

    for func in get_raw, get_core, get_orm:
        start = datetime.now()
        rows = len(func(engine))
        print(f'{func.__name__} got {rows} rows, time: {datetime.now() - start}')

sqlalchemy version 1.2.19 oracle 12c

There is a performance page in SQLAlchemy documentation but I found it unhelpful.

Please feel free to run this code and share your results. Thank you.

Edit:

What is your question?

The question is what kind of defect leads to this and how to fix SQLAlchemy performance with Oracle.

The disparity is very large and cannot be explained by "sqlalchemy does more": I added code to wrap all rows received from raw connection into ORM classes and it only added a small fraction to the total time.

I observe it with all our tables and views. With larger loads it scales up to stay at 2x. E.g. Raw connections fetches records in 12 seconds, while SQLAlchemy takes 24.

Upvotes: 1

Views: 1012

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

First off, SQLAlchemy 1.2.19 is pretty old (released 2019-04-15, over 3 years ago). That said …

Benchmarking can be tricky because the results may depend on many factors. I ran your test on a 10-year-old notebook using SQLAlchemy 1.4.36 and cx-Oracle 8.3.0 and I got

output rows/sec remarks
get_raw got 10000 rows, time: 0:00:01.672070 5981
get_core got 10000 rows, time: 0:00:02.065249 4842 19% less than raw
get_orm got 10000 rows, time: 0:00:02.752992 3632 25% less than core

The raw DBAPI connection appears to be significantly faster, but remember that SQLAlchemy is doing more work. cx-Oracle ("raw") returns a list of simple tuples. SQLAlchemy Core returns SQLAlchemy Row-like objects that offer additional features, and SQLAlchemy ORM goes even farther by converting each row into an ORM object.

Your post title claims that SQLAlchemy is twice as slow as cx-Oracle. My results show that not to be true, at least not always.

Upvotes: 2

Related Questions