pandasで.csvを扱うのとsqliteで.dbを扱うの、どちらが速いのか

# 初めに

いつもcsvファイルをpandasで読み書きして扱ってますが、もしかしたらsqliteで小規模なデータベースをサクッと作ってクエリを流す方が速いのではないか？とふと思いました。それの検証をしてみます。

# 実験

## データの読み書き

### dummy dataの作成

```python
NCOLS = 100
NROWS = 100_000

# dummy data作成
cols = [f'c{i}' for i in range(NCOLS)]
np.random.seed(0)
dummy = np.random.rand(NROWS, NCOLS)

# csvに保存
pd.DataFrame(dummy, columns=cols).to_csv('dummy.csv')
```

上記の条件の場合、dummy.csvは189MB程度になる。

### pandas

```python
# pandas read
%timeit pd.read_csv('dummy.csv')
```
> 2.12 s ± 128 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

```python
# pandas write
df = pd.read_csv('dummy.csv')
%timeit df.to_csv('dummy2.csv')
```
> 12.3 s ± 427 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

### pandas + pickle

```python
# pandas pickle write
%timeit df.to_pickle('dummy.pickle')
```
> 984 ms ± 42.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

```python
# pandas pickle read
%timeit pd.read_pickle('dummy.pickle')
```
> 31.5 ms ± 1.71 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

### sqlite (cur.fetchall())

```python
import time

t0 = time.time() # start
dbname = 'test2.db'
conn = sqlite3.connect(dbname)
# cursor for manipulating tables
cur = conn.cursor()

# make table
t_table = time.time() # checkpoint
create_table_items = ', '.join([f'c{i} FLOAT' for i in range(NCOLS)])
cur.execute(
    f'CREATE TABLE items({create_table_items})'
)

# check only the bottleneck part.
t_insert = time.time() # checkpoint
val_questions = ', '.join(['?'] * NCOLS)
cur.executemany(f'INSERT INTO items values({val_questions})', dummy)
conn.commit()

# read from db
t_select = time.time() # checkpoint
cur.execute('SELECT * FROM items')
df = pd.DataFrame(cur.fetchall())
df.columns = [d[0] for d in cur.description]
t_end = time.time()

print('time stats')
print('----------')
print('init', t_table - t0)
print('table', t_insert - t_table)
print('insert', t_select - t_insert)
print('select', t_end - t_select)

cur.close()
conn.close()
```
> time stats
> ----------
> init 0.0010020732879638672
> table 0.14599847793579102
> insert 6.390852928161621
> select 4.492218255996704

### clean the db

```python
conn = sqlite3.connect(dbname)
# cursor for manipulating tables
cur = conn.cursor()
cur.execute('drop table items')
cur.close()
conn.close()
```

### sqlite (pandas)
```python
import time

t0 = time.time() # start
dbname = 'test2.db'
conn = sqlite3.connect(dbname)
# cursor for manipulating tables
cur = conn.cursor()

# insert
t_insert = time.time() # checkpoint
df.to_sql('items', conn, if_exists='append', index=None)
conn.commit()

# read from db
t_select = time.time() # checkpoint
df = pd.read_sql_query('SELECT * FROM items', conn)
t_end = time.time()

print('time stats')
print('----------')
print('init', t_insert - t0)
print('insert', t_select - t_insert)
print('select', t_end - t_select)

cur.close()
conn.close()
```

> time stats
> ----------
> init 0.001001119613647461
> insert 4.4236931800842285
> select 4.559115409851074

## ローカルファイルからデータを読んでフィルター

### pandas + pickle

```python
df = pd.read_pickle('dummy.pickle')
%timeit df2 = df[(df.c1 > 0.048) & (df.c3 < 0.8812)][['c4', 'c10', 'c99']]
```
> 31.1 ms ± 1.63 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

### sqlite + pandas

```python 
dbname = 'test2.db'
conn = sqlite3.connect(dbname)
# cursor for manipulating tables
cur = conn.cursor()

# read from db
%timeit df = pd.read_sql_query('SELECT c4, c10, c99 FROM items where c1 > 0.048 and c3 < 0.8812', conn)

cur.close()
conn.close()
```

> 251 ms ± 6.87 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# 結果

# 結果

データの読み書きの結果は以下だった。

(数値は全て秒)
|項目|read|write|
|----|----|----|
|pandas|2.12|12.3|
|pandas pickle|0.984|0.032|
|sqlite (cur.fetchall())|4.49|6.39|
|sqlite (pandas)|4.56|4.42

pickleを使う手法が最も高速だった。

また条件式でデータをフィルターした場合の結果は以下だった。

(数値は全て秒)
|項目|read|
|----|----|
|pandas|0.031|
|sqlite|0.251|

pd.read_pickle()の所要時間32msecを考慮しても、pandasの方がsqliteより4倍近く高速だった。

# 考察

pandasは内部でCythonもしくはCが動作しているため、高速である。そして、sqliteもCで書かれているため高速だが、RDBMSとしての仕様が足枷になりpandasより低速化している可能性がある。現に、sqliteのdbファイルはpandasでデータを保存したcsvファイルが189MBなのに対し、2.4GBと肥大していた。


# 結論

データのローカルへの読み書きにはpandasのpickle関連の関数を、フィルターなどのデータ操作はpickleを読みだした後、pandasの通常の文法で実施するのが最良である。


# Future work

polarsと比較した場合結果がどうなるかは気になる。
また、データサイズを変えてみた場合結論は一貫しているのかは確かめる必要がある。