[解決済み] SQLAlchemyのsqliteによる挿入は、なぜsqlite3を直接使うよりも25倍も遅いのですか？

質問

この単純なテストケースの 100,000 行の挿入が、SQLAlchemy を使った場合、sqlite3 ドライバを直接使った場合よりも 25 倍も遅いのはなぜですか？ 実際のアプリケーションでも同じような速度低下を見たことがあります。 私は何か間違ったことをしているのでしょうか？

#!/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)

私は数多くのバリエーションを試しました ( http://pastebin.com/zCmzDraU )

どのように解決するのですか？

SQLAlchemy ORM は 作業単位 パターンを使います。 このパターンは、単純なデータの挿入をはるかに超えています。 オブジェクトに割り当てられた属性は、オブジェクトの変更が行われるとそれを追跡する属性計装システムを使用して受信されること、挿入されたすべての行が アイデンティティマップ これは、SQLAlchemy が各行に対して "最後に挿入された id" を取得しなければならない、という効果を持ちます。また、挿入される行は、必要に応じてスキャンされ、依存関係のためにソートされます。 オブジェクトはまた、これらすべてを実行し続けるために、かなりの度合いで帳簿をつけることになります。

基本的に、作業単位は、複雑なオブジェクト グラフをリレーショナル データベースに明示的な永続化コードなしで永続化するタスクを自動化するための大規模な自動化であり、この自動化には代償があります。

ですから、ORM は基本的に高性能な一括挿入を意図したものではありません。 これが、SQLAlchemy が に の別ライブラリを持っている理由です。 http://docs.sqlalchemy.org/en/latest/index.html を見ると、インデックスページが2つに分かれているのがわかるでしょう - 1つは ORM 用、もう1つは Core 用です。 SQLAlchemy を効果的に使うには、その両方を理解する必要があります。

高速な一括挿入のユースケースのために、SQLAlchemy は コア これは ORM がその上に構築する、SQL の生成と実行のシステムです。 このシステムを効果的に使えば、生の SQLite バージョンと競争力のある INSERT を作ることができます。 以下のスクリプトは、ORM が行を挿入するために executemany() を使用できるように主キー識別子を事前に割り当てる ORM バージョンと同様に、これを図示しています。 どちらの 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

スクリプトを使用します。

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