python - example - sqlalchemy sqlite

¿Por qué SQLAlchemy se inserta con sqlite 25 veces más lento que usando sqlite3 directamente?

Question

¿Por qué este simple caso de prueba inserta 100.000 filas 25 veces más lento con SQLAlchemy que con el controlador sqlite3 directamente? He visto ralentizaciones similares en aplicaciones del mundo real. ¿Estoy haciendo algo mal?

#!/usr/bin/env python
# Why is SQLAlchemy with SQLite so slow?
# Output from this program:
# SqlAlchemy: Total time for 100000 records 10.74 secs
# sqlite3:    Total time for 100000 records  0.40 secs


import time
import sqlite3

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,  create_engine 
from sqlalchemy.orm import scoped_session, sessionmaker

Base = declarative_base()
DBSession = scoped_session(sessionmaker())

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))

def init_sqlalchemy(dbname = ''sqlite:///sqlalchemy.db''):
    engine  = create_engine(dbname, echo=False)
    DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

def test_sqlalchemy(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer()
        customer.name = ''NAME '' + str(i)
        DBSession.add(customer)
    DBSession.commit()
    print "SqlAlchemy: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"

def init_sqlite3(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS customer")
    c.execute("CREATE TABLE customer (id INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY(id))")
    conn.commit()
    return conn

def test_sqlite3(n=100000, dbname = ''sqlite3.db''):
    conn = init_sqlite3(dbname)
    c = conn.cursor()
    t0 = time.time()
    for i in range(n):
        row = (''NAME '' + str(i),)
        c.execute("INSERT INTO customer (name) VALUES (?)", row)
    conn.commit()
    print "sqlite3: Total time for " + str(n) + " records " + str(time.time() - t0) + " sec"

if __name__ == ''__main__'':
    test_sqlalchemy(100000)
    test_sqlite3(100000)

He intentado numerosas variaciones (ver http://pastebin.com/zCmzDraU )

Answer 1

El SQLAlchemy ORM utiliza el patrón de unidad de trabajo al sincronizar los cambios en la base de datos. Este patrón va más allá de simples "insertos" de datos. Incluye que los atributos asignados en los objetos se reciben usando un sistema de instrumentación de atributos que rastrea los cambios en los objetos a medida que se crean, incluye que todas las filas insertadas se rastrean en un mapa de identidad que tiene como efecto que cada fila SQLAlchemy debe recuperar su " última identificación insertada "si no está ya incluida, y también implica que las filas que se insertarán se escanean y se ordenan para las dependencias según sea necesario. Los objetos también están sujetos a un buen grado de contabilidad para mantener todo esto en ejecución, lo que para una gran cantidad de filas a la vez puede crear una cantidad excesiva de tiempo dedicado a grandes estructuras de datos, por lo tanto, es mejor dividirlas.

Básicamente, la unidad de trabajo es un alto grado de automatización para automatizar la tarea de persistir en un gráfico complejo de objetos en una base de datos relacional sin código de persistencia explícito, y esta automatización tiene un precio.

Por lo tanto, los ORM básicamente no están destinados a inserciones de gran volumen de alto rendimiento. Esta es la razón por la cual SQLAlchemy tiene dos bibliotecas separadas, que notarás si miras en http://docs.sqlalchemy.org/en/latest/index.html verás dos mitades distintas en la página de índice: uno para el ORM y uno para el Core. No puede usar SQLAlchemy efectivamente sin entender ambos.

Para el caso de uso de inserciones masivas rápidas, SQLAlchemy proporciona el core , que es el sistema de generación y ejecución de SQL sobre el que el ORM construye. Usando este sistema de manera efectiva, podemos producir un INSERT que sea competitivo con la versión SQLite sin formato. El script a continuación ilustra esto, así como una versión de ORM que asigna previamente los identificadores de clave primaria para que el ORM pueda usar executemany () para insertar filas. Ambas versiones de ORM también bloquean las descargas en 1000 registros a la vez, lo que tiene un impacto significativo en el rendimiento.

Los tiempos de ejecución observados aquí son:

SqlAlchemy ORM: Total time for 100000 records 16.4133379459 secs
SqlAlchemy ORM pk given: Total time for 100000 records 9.77570986748 secs
SqlAlchemy Core: Total time for 100000 records 0.568737983704 secs
sqlite3: Total time for 100000 records 0.595796823502 sec

guión:

import time
import sqlite3

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,  create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

Base = declarative_base()
DBSession = scoped_session(sessionmaker())

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))

def init_sqlalchemy(dbname = ''sqlite:///sqlalchemy.db''):
    global engine
    engine = create_engine(dbname, echo=False)
    DBSession.remove()
    DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

def test_sqlalchemy_orm(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer()
        customer.name = ''NAME '' + str(i)
        DBSession.add(customer)
        if i % 1000 == 0:
            DBSession.flush()
    DBSession.commit()
    print "SqlAlchemy ORM: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"

def test_sqlalchemy_orm_pk_given(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer(id=i+1, name="NAME " + str(i))
        DBSession.add(customer)
        if i % 1000 == 0:
            DBSession.flush()
    DBSession.commit()
    print "SqlAlchemy ORM pk given: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"

def test_sqlalchemy_core(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    engine.execute(
        Customer.__table__.insert(),
        [{"name":''NAME '' + str(i)} for i in range(n)]
    )
    print "SqlAlchemy Core: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"

def init_sqlite3(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS customer")
    c.execute("CREATE TABLE customer (id INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY(id))")
    conn.commit()
    return conn

def test_sqlite3(n=100000, dbname = ''sqlite3.db''):
    conn = init_sqlite3(dbname)
    c = conn.cursor()
    t0 = time.time()
    for i in range(n):
        row = (''NAME '' + str(i),)
        c.execute("INSERT INTO customer (name) VALUES (?)", row)
    conn.commit()
    print "sqlite3: Total time for " + str(n) + " records " + str(time.time() - t0) + " sec"

if __name__ == ''__main__'':
    test_sqlalchemy_orm(100000)
    test_sqlalchemy_orm_pk_given(100000)
    test_sqlalchemy_core(100000)
    test_sqlite3(100000)

Ver también: http://docs.sqlalchemy.org/en/latest/faq/performance.html

Answer 2

Excelente respuesta de @zzzeek. Para aquellos que se preguntan acerca de las mismas estadísticas para las consultas, modifiqué ligeramente el código @zzzeek para consultar esos mismos registros justo después de insertarlos y luego convertir esos registros a una lista de dicts.

Aquí están los resultados

SqlAlchemy ORM: Total time for 100000 records 11.9210000038 secs
SqlAlchemy ORM query: Total time for 100000 records 2.94099998474 secs
SqlAlchemy ORM pk given: Total time for 100000 records 7.51800012589 secs
SqlAlchemy ORM pk given query: Total time for 100000 records 3.07699990273 secs
SqlAlchemy Core: Total time for 100000 records 0.431999921799 secs
SqlAlchemy Core query: Total time for 100000 records 0.389000177383 secs
sqlite3: Total time for 100000 records 0.459000110626 sec
sqlite3 query: Total time for 100000 records 0.103999853134 secs

Es interesante notar que consultar utilizando sqlite3 desnudo es todavía aproximadamente 3 veces más rápido que usar SQLAlchemy Core. Supongo que ese es el precio que paga por tener un ResultProxy devuelto en lugar de una fila sqlite3 desnuda.

SQLAlchemy Core es aproximadamente 8 veces más rápido que el uso de ORM. Entonces consultar con ORM es mucho más lento sin importar qué.

Aquí está el código que utilicé:

import time
import sqlite3

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,  create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql import select

Base = declarative_base()
DBSession = scoped_session(sessionmaker())

class Customer(Base):
    __tablename__ = "customer"
    id = Column(Integer, primary_key=True)
    name = Column(String(255))

def init_sqlalchemy(dbname = ''sqlite:///sqlalchemy.db''):
    global engine
    engine = create_engine(dbname, echo=False)
    DBSession.remove()
    DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

def test_sqlalchemy_orm(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer()
        customer.name = ''NAME '' + str(i)
        DBSession.add(customer)
        if i % 1000 == 0:
            DBSession.flush()
    DBSession.commit()
    print "SqlAlchemy ORM: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"
    t0 = time.time()
    q = DBSession.query(Customer)
    dict = [{''id'':r.id, ''name'':r.name} for r in q]
    print "SqlAlchemy ORM query: Total time for " + str(len(dict)) + " records " + str(time.time() - t0) + " secs"


def test_sqlalchemy_orm_pk_given(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    for i in range(n):
        customer = Customer(id=i+1, name="NAME " + str(i))
        DBSession.add(customer)
        if i % 1000 == 0:
            DBSession.flush()
    DBSession.commit()
    print "SqlAlchemy ORM pk given: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"
    t0 = time.time()
    q = DBSession.query(Customer)
    dict = [{''id'':r.id, ''name'':r.name} for r in q]
    print "SqlAlchemy ORM pk given query: Total time for " + str(len(dict)) + " records " + str(time.time() - t0) + " secs"

def test_sqlalchemy_core(n=100000):
    init_sqlalchemy()
    t0 = time.time()
    engine.execute(
        Customer.__table__.insert(),
        [{"name":''NAME '' + str(i)} for i in range(n)]
    )
    print "SqlAlchemy Core: Total time for " + str(n) + " records " + str(time.time() - t0) + " secs"
    conn = engine.connect()
    t0 = time.time()
    sql = select([Customer.__table__])
    q = conn.execute(sql)
    dict = [{''id'':r[0], ''name'':r[0]} for r in q]
    print "SqlAlchemy Core query: Total time for " + str(len(dict)) + " records " + str(time.time() - t0) + " secs"

def init_sqlite3(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS customer")
    c.execute("CREATE TABLE customer (id INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY(id))")
    conn.commit()
    return conn

def test_sqlite3(n=100000, dbname = ''sqlite3.db''):
    conn = init_sqlite3(dbname)
    c = conn.cursor()
    t0 = time.time()
    for i in range(n):
        row = (''NAME '' + str(i),)
        c.execute("INSERT INTO customer (name) VALUES (?)", row)
    conn.commit()
    print "sqlite3: Total time for " + str(n) + " records " + str(time.time() - t0) + " sec"
    t0 = time.time()
    q = conn.execute("SELECT * FROM customer").fetchall()
    dict = [{''id'':r[0], ''name'':r[0]} for r in q]
    print "sqlite3 query: Total time for " + str(len(dict)) + " records " + str(time.time() - t0) + " secs"


if __name__ == ''__main__'':
    test_sqlalchemy_orm(100000)
    test_sqlalchemy_orm_pk_given(100000)
    test_sqlalchemy_core(100000)
    test_sqlite3(100000)

También probé sin convertir el resultado de la consulta a dicts y las estadísticas son similares:

SqlAlchemy ORM: Total time for 100000 records 11.9189999104 secs
SqlAlchemy ORM query: Total time for 100000 records 2.78500008583 secs
SqlAlchemy ORM pk given: Total time for 100000 records 7.67199993134 secs
SqlAlchemy ORM pk given query: Total time for 100000 records 2.94000005722 secs
SqlAlchemy Core: Total time for 100000 records 0.43700003624 secs
SqlAlchemy Core query: Total time for 100000 records 0.131000041962 secs
sqlite3: Total time for 100000 records 0.500999927521 sec
sqlite3 query: Total time for 100000 records 0.0859999656677 secs

Consultar con SQLAlchemy Core es aproximadamente 20 veces más rápido en comparación con ORM.

Es importante tener en cuenta que esas pruebas son muy superficiales y no deben tomarse demasiado en serio. Me pueden estar perdiendo algunos trucos obvios que podrían cambiar las estadísticas por completo.

La mejor forma de medir las mejoras de rendimiento está directamente en su propia aplicación. No tomes mis estadísticas por sentado.

Answer 3

Intentaría la prueba de expresión de inserción y luego compararía.

Probablemente aún sea más lento debido a la sobrecarga de OR o el mapeador, pero espero que no sea mucho más lento.

¿Te importaría probar y publicar resultados? Esto es algo muy interesante.