初めに

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

実験

データの読み書き

dummy dataの作成

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

# pandas read
%timeit pd.read_csv('dummy.csv')

2.12 s ± 128 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# 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

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

# 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())

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

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

sqlite (pandas)

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

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

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