1億件 INSERT 実験所【2019 アドカレ】

概要

sqlite3 ライブラリと sqlalchemy ライブラリを使って、 SQLite への1億件 INSERT の速度を比べてみます。

1億件突っ込まれる table

こういう table をメモリ上に作成し、そこへ INSERT していきます。

| id | c1 | c2 | c3 | c4 | c5 |
|----|----|----|----|----|----|
|  1 | c1 | c2 | c3 | c4 | c5 |
|  2 | c1 | c2 | c3 | c4 | c5 |
...

id カラムには AI(オートインクリメント)を設定します。ただ、 AI 抜きの状態での INSERT も試してみます。

結果

100万件で試した場合

ライブラリ	条件	経過秒数
sqlite3	1件ずつ	3.37001
	いっぺんに	2.45202
	いっぺんに、かつ AI 抜き	2.50639
sqlalchemy	1件ずつ	77.00850
	いっぺんに	75.82006
	いっぺんに、かつ AI 抜き	54.97433

1億件で試した場合

ライブラリ	条件	経過秒数
sqlite3	1件ずつ	344.67605
	いっぺんに	251.14147
	いっぺんに、かつ AI 抜き	255.33229
sqlalchemy	1件ずつ	MemoryError
	いっぺんに	MemoryError
	いっぺんに、かつ AI 抜き	MemoryError

sqlite3

実験に使った sqlite3 コードを載せます。

1件ずつ

import sqlite3
from contextlib import closing
import time


HUNDRED_MILLION = 100_000_000
INSERT_NUM = HUNDRED_MILLION


def sqlite_1():
    """AI あり、1件ずつ。"""

    # メモリ上に sqlite を作成します。
    # NOTE: closing を使うことで con.close() を書く必要がなくなります。
    with closing(sqlite3.connect(':memory:')) as con:

        # テーブル作成。
        con.execute('CREATE TABLE tbl(id INTEGER PRIMARY KEY AUTOINCREMENT, c1 TEXT, c2 TEXT, c3 TEXT, c4 TEXT, c5 TEXT)')

        # INSERT データ作成。
        values = (
            { 'c1':'c1','c2':'c2','c3':'c3','c4':'c4','c5':'c5', }
            for i in range(INSERT_NUM)
        )

        # INSERT 開始。
        with con:
            # 1件ずつ INSERT します。
            for value in values:
                con.execute('INSERT INTO tbl(c1,c2,c3,c4,c5) VALUES (:c1,:c2,:c3,:c4,:c5)', value)


# 計測します。
start = time.time()
sqlite_1()
print(round(time.time() - start, 5))

いっぺんに

def sqlite_2():
    """AI あり、全件同時。"""

    with closing(sqlite3.connect(':memory:')) as con:
        con.execute('CREATE TABLE tbl(id INTEGER PRIMARY KEY AUTOINCREMENT, c1 TEXT, c2 TEXT, c3 TEXT, c4 TEXT, c5 TEXT)')
        values = (
            { 'c1':'c1','c2':'c2','c3':'c3','c4':'c4','c5':'c5', }
            for i in range(INSERT_NUM)
        )
        with con:
            # executemany で全件同時に INSERT します。
            con.executemany('INSERT INTO tbl(c1,c2,c3,c4,c5) VALUES (:c1,:c2,:c3,:c4,:c5)', values)


start = time.time()
sqlite_2()
print(round(time.time() - start, 5))

いっぺんに、かつ AI 抜き

def sqlite_3():
    """AI なし、全件同時。"""

    with closing(sqlite3.connect(':memory:')) as con:
        # AI を外して table 定義します。
        con.execute('CREATE TABLE tbl(id INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT, c3 TEXT, c4 TEXT, c5 TEXT)')
        values = (
            { 'id':i,'c1':'c1','c2':'c2','c3':'c3','c4':'c4','c5':'c5', }
            for i in range(INSERT_NUM)
        )
        with con:
            con.executemany('INSERT INTO tbl(id, c1,c2,c3,c4,c5) VALUES (:id,:c1,:c2,:c3,:c4,:c5)', values)


start = time.time()
sqlite_3()
print(round(time.time() - start, 5))

sqlalchemy

実験に使った sqlalchemy コードを載せます。

1件ずつ

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


HUNDRED_MILLION = 100_000_000
INSERT_NUM = HUNDRED_MILLION


def alchemy_1():
    """AI あり、1件ずつ。"""

    # メモリ上に sqlite を作成します。
    engine = create_engine('sqlite:///:memory:')

    # テーブル作成。
    Base = declarative_base()
    class Tbl(Base):
        __tablename__ = 'tbl'
        id = Column(Integer, primary_key=True, autoincrement=True)
        c1 = Column(String(10))
        c2 = Column(String(10))
        c3 = Column(String(10))
        c4 = Column(String(10))
        c5 = Column(String(10))
    Base.metadata.create_all(engine)

    # セッション開始。
    session = sessionmaker(bind=engine)()

    # INSERT データ作成。
    values = (
        Tbl(c1='c1',c2='c2',c3='c3',c4='c4',c5='c5')
        for i in range(INSERT_NUM)
    )

    # 1件ずつ INSERT します。
    for value in values:
        session.add(value)
    session.commit()


start = time.time()
alchemy_1()
print(round(time.time() - start, 5))

いっぺんに

def alchemy_2():
    """AI あり、全件同時。"""

    engine = create_engine('sqlite:///:memory:')
    Base = declarative_base()
    class Tbl(Base):
        __tablename__ = 'tbl'
        id = Column(Integer, primary_key=True, autoincrement=True)
        c1 = Column(String(10))
        c2 = Column(String(10))
        c3 = Column(String(10))
        c4 = Column(String(10))
        c5 = Column(String(10))
    Base.metadata.create_all(engine)
    session = sessionmaker(bind=engine)()
    values = (
        Tbl(c1='c1',c2='c2',c3='c3',c4='c4',c5='c5')
        for i in range(INSERT_NUM)
    )
    # add_all で全件同時に INSERT します。
    session.add_all(values)
    session.commit()


start = time.time()
alchemy_2()
print(round(time.time() - start, 5))

いっぺんに、かつ AI 抜き

def alchemy_3():
    """AI なし、全件同時。"""

    engine = create_engine('sqlite:///:memory:')
    Base = declarative_base()
    class Tbl(Base):
        __tablename__ = 'tbl'
        # AI を外して table 定義します。
        id = Column(Integer, primary_key=True)
        c1 = Column(String(10))
        c2 = Column(String(10))
        c3 = Column(String(10))
        c4 = Column(String(10))
        c5 = Column(String(10))
    Base.metadata.create_all(engine)
    session = sessionmaker(bind=engine)()
    values = (
        Tbl(id=i, c1='c1',c2='c2',c3='c3',c4='c4',c5='c5')
        for i in range(INSERT_NUM)
    )
    session.add_all(values)
    session.commit()


start = time.time()
alchemy_3()
print(round(time.time() - start, 5))

どうしてこんなことを

「テストするときは DB に1億件くらい入っている状態でしたいねー」という話をどこかで読み、それって用意するのにどれくらい時間がかかるのだろう、と思ったので試しました。

ORM の sqlalchemy は遅かったですが、 DB の構造がコード上で表現されるところが大好きです。