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

## 概要

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

## 1億件突っ込まれる table

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

```plaintext
| 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件ずつ

```python
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))
```

### いっぺんに

```python
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 抜き

```python
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件ずつ

```python
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))
```

### いっぺんに


```python
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 抜き


```python
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 の構造がコード上で表現されるところが大好きです。