SQLiteからDataFrameへの読み込みに時間がかかりすぎる

SQLite形式でデータベースを作り、データの前処理をするためにPandasのDataFrameに読み込もうとしています。ただ、この読み込みに非常に時間がかかって困っております。

周波数×時間の構造を取るデータのため、列数が多く、大きさとしては1000列×24万行というデータです。また、同じdbファイルには同様のデータが10テーブルほどあります（ファイルは1つだが、テーブルは分けています）。

以下コードのように、単純なselect * でも読み込みに数分かかってしまう状況です。なお、コンソールからselect * を実行すると3秒程度で読み込めます。

Python
1import pandas as pd
2import sqlite3
3
4with sqlite3.connect('master.db') as conn:
5    df = pd.read_sql_query(sql=f'select * from table', con=conn, index_col='time')

直接の回答ではなくても、この種類のデータならこのSQLを使った方がいいという回答でも大変助かります。

hoshi-takanori

2020/04/20 09:47

1000列×24万行なら、各要素が double (8 バイト) とすると約 2GB ですね。数分で読み込めるならむしろ高速と思ってしまいます。

MagMag

2020/04/20 10:55

ありがとうございます。コンソールで数秒で、pandasで数分という差が埋まればいいのですが、、、。

hoshi-takanori

2020/04/20 10:58

コンソールというのは sqlite3 コマンドの中で select してるって意味ですか？ 24万行を数秒で表示し切れるんですか？

MagMag

2020/04/21 01:04 編集

数秒というのは、PyCharmのconsoleからselect *を実施した場合でした。これは数秒で表示されますが、PyCharmでは500件ずつ分けて表示するようになっていたので、コマンドで叩いた操作と違うことがわかりました。失礼しました。 sqlite3からmysqlに変えてしまったのですが、mysqlから直接select *を打つと、データが出てくるまで30秒くらい、その後、結果表示を5分くらいという感じでした。そうすると、速度の問題を解決するなら、データベースの設計を変える必要がありそうなので、関連知識を調べていきたいと思います。正規化とprimary keyの工夫が頭に思い浮かびますが、もし参考となるキーワードがあれば教えていただけたら大変助かります。

hoshi-takanori

2020/04/21 01:22 編集

一般的なデータベースの設計論というのは、事務処理的なアプリケーションが前提となっているというか、例えば正規化はデータの整合性を保つためのもので、速くするためのものではありませんし、primary key (というか index) は 24 万件の中から特定のものを素早く探し出すためのもので、全件を取り出すには当然それだけの時間がかかります。また、データをすべて読み込むにはデータベース側だけでなく、python 側で受け取ったデータをメモリ上に配列として展開する必要がある訳で、むしろこっちに時間がかかってる可能性もあります。

hoshi-takanori

2020/04/21 01:21

とりあえず「python データ処理高速化」でググったらこんなのが引っかかりました。 https://qiita.com/kishiyama/items/b9d0a7ba3f03fd4089f3 https://qiita.com/KTaskn/items/07e49a4f21e9afdad35f https://codezine.jp/article/detail/8687 https://shinyorke.hatenablog.com/entry/pandas-tips

hoshi-takanori

2020/04/21 01:30

いずれにせよ、一般的なアプリケーションであれば高速化のコツは「サボること」です。例えば最初の 500 件だけ表示するとか、本当に必要になるまで計算しないとか、以前の計算結果を覚えておいて差分だけ計算するとか。ですが、おそらく MagMag さんの場合はガチで全部のデータを使って計算したいのでしょうから、頑張って計算するしかないのでしょうし、2GB のデータはそれなりに大きくてまともに処理したら時間がかかって当たり前、という感覚は持っておいた方がいいかと思います。

MagMag

2020/04/21 01:33

ありがとうございます。SQLからデータを取るところではなく、Python側の方で工夫してみます。もし、すでにいただいた回答をメインの方にコピペしてもらえれば、ベストアンサーにさせていただきます。