Почему SQLAlchemy вставляет sqlite в 25 раз медленнее, чем использование sqlite3 напрямую?

Ответ 1

SQLAlchemy ORM использует элемент работы при синхронизации изменений в базе данных. Этот шаблон выходит далеко за рамки простых "вставок" данных. Он включает в себя то, что атрибуты, назначенные объектам, принимаются с использованием системы инструментов атрибутов, которая отслеживает изменения объектов по мере их создания, включает в себя, что все вставленные строки отслеживаются в что для каждой строки SQLAlchemy должен получить свой "последний вставленный идентификатор" , если он еще не задан, а также включает, что строки, которые нужно вставить, сканируются и сортируются по зависимостям по мере необходимости. Объекты также подвержены достоверной степени бухгалтерского учета, чтобы сохранить все это, что для очень большого количества строк сразу может создать чрезмерное количество времени, затрачиваемого на большие структуры данных, поэтому лучше всего их обрезать.

В основном, единица работы - это большая степень автоматизации, чтобы автоматизировать задачу сохранения сложного графа объектов в реляционную базу данных без явного кода сохранения, и эта автоматизация имеет цену.

Таким образом, ORM в основном не предназначены для высокопроизводительных объемных вставок. Вот почему SQLAlchemy имеет две отдельные библиотеки, которые вы заметите, если вы посмотрите на http://docs.sqlalchemy.org/en/latest/index.html, вы увидите две разные половины на индексную страницу - одну для ORM и одну для Core. Вы не можете эффективно использовать SQLAlchemy, не понимая оба.

В случае использования быстрых объемных вставок SQLAlchemy предоставляет core, который представляет собой систему генерации и выполнения SQL, которую строят ORM на вершине. Используя эту систему, мы можем создать INSERT, который является конкурентоспособным с исходной версией SQLite. Ниже приведено описание script, а также версия ORM, которая предварительно назначает идентификаторы первичного ключа, чтобы ORM могла использовать функцию executeemany() для вставки строк. Обе версии ORM объединяют флеши с 1000 записями за раз, что имеет значительное влияние на производительность.

Наблюдаемые здесь промежутки времени:

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

script:

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)

Смотрите также: http://docs.sqlalchemy.org/en/latest/faq/performance.html

Ответ 2

Отличный ответ от @zzzeek. Для тех, кто интересуется одной и той же статистикой запросов, я немного изменил код @zzzeek, ​​чтобы запросить те же записи сразу после их вставки, а затем преобразовать эти записи в список dicts.

Здесь результаты

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

Интересно отметить, что запрос с использованием голого sqlite3 все еще примерно в 3 раза быстрее, чем использование SQLAlchemy Core. Я предполагаю, что цена, которую вы платите за ResultProxy, возвращается вместо годовой строки sqlite3.

SQLAlchemy Core примерно в 8 раз быстрее, чем использование ORM. Таким образом, запрос с использованием ORM намного медленнее, независимо от того, что.

Вот код, который я использовал:

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)

Я также тестировал, не преобразовывая результат запроса в dicts, и статистика аналогична:

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

Запрос с помощью SQLAlchemy Core примерно в 20 раз быстрее по сравнению с ORM.

Важно отметить, что эти тесты очень поверхностны и не должны восприниматься слишком серьезно. Я мог бы пропустить некоторые очевидные трюки, которые могли бы полностью изменить статистику.

Лучший способ измерения производительности - прямо в вашем собственном приложении. Не принимайте мою статистику как должное.

Ответ 3

Я бы попробовал вставить выражение, а затем выполнить тест.

Вероятно, он все еще будет медленнее из-за накладных расходов OR mapper, но я надеюсь, что не намного медленнее.

Не могли бы вы попробовать и опубликовать результаты. Это очень интересный материал.