2019/06/16

64コアCPUで動くExcelビッグデータ高速化の研究

このページは随時アップデートされ、そのうちに独立したコーナーとして記事になります。
そして、もしかしたら本になるかもしれません。
現在は記事を蓄積中。

テーマとしては、
・Excelで104万行を超えるビッグデータを扱う
・Power Query(データモデル)とPower Pivot
・遅い、共有できない など脱Excelの主張に対して、それは使い方が悪いからだという反論
・どの処理が速い、どの処理は遅いなどの比較
・バグ情報
・Excelでは通常できない、PDF編集、スクレイピング、Windows自動化、python連携などのやり方

遅い処理と速い処理の基本方針

速い処理と遅い処理を熟知する

Excelででかいデータを扱うときは、ソフトウェアの作り方ではなく、ASICのような?作り方をすると速いです。
たとえば、8つに分岐する処理があれば、ループを8回するよりも、シートを8枚作って、そこにあらかじめ数式を敷き詰めて、処理を待ち受けると速いです。繰り返したり分岐したりせず、一方通行の流れを作ると速いです。Excelは巨大なピタゴラスイッチだと思って作るとよいでしょう。

処理結果によって、出てくる件数が可変。というような場合でも対応できます。
オートフィル→最後の有効列まで など、毎回の可変処理対応をすると(セルへの書き込みが発生して)遅くなるのでこれはしないほうがよいです。
むしろ、出てくる値の最大限に合わせて、あらかじめ数式を敷き詰めておくと無駄が多いように見えますがじつは速いです(数式がセルに入っている状態で再計算が発生して値が変化する時は速い)
数式を並べた数>出てきた結果数 となり、数式部分が余った場合はオートフィルタなどで切り捨てるか、最後の最後にfor nextループで結果の値だけ回収するようにしましょう。

つまり、効率の良さそうなコードを書くよりも、無駄にCPUが酷使される仕事のほうが結局は速いのです。これは、どうやら、シングルスレッドで動くVBAのfor nextループやセル操作は、セルへの書き込みもシングルスレッドでしているようで、演算よりもセルへの書き込みに時間を取られている様子です。
一方、マルチスレッド対応の関数は、計算結果をマルチスレッドでセル(メモリ)に書き込むようで、今回のように32コアあれば、おそらく32倍速くなります。

Excelは最大1024スレッド対応らしいので、うまくやれば、とんでもない規模のビッグデータ処理もできるはずです。なお、GPGPUアドインのようなものは存在しないようですので、論理256コアの構成でも8Way-Xeonで1千万円くらいかかります。あり得ない富裕層構成。と思いきや、金融データの分析で有用な結果が出せるならば、文字通り秒速で回収できますので、1千万円のPCでExcelを動かしても採算が取れるという夢があります。

シート上で操作すると重い処理はPower Queryに入れて処理する

データモデルの操作はPower Queryでやるのでシート+関数という発想ではなく、SQLになるので慣れないとややこしいですが、慣れれば高速化の恩恵にあずかれます。
なお、データモデルと混同しやすいですが、データモデル(Power Pivot)ではなく、Power Queryで編集>データモデルに入れる>Power Pivotで集計 という流れです。
私がPower Queryを研究した記録はこちら

コピー・ペーストを繰り返さない

大きなデータのコピー&ペーストを繰り返すVBAを書くと、メモリリークして回復不能となるので避けましょう。
今回のアプリも最終的には200万セル以上を使っているにもかかわらず、500MBほどのメモリ使用量で済みましたが、初期にコピペを繰り返すVBAで書いていたときは15GBものメモリを消費していました。
メモリを浪費するだけなら、メモリを追加すればいいのですが、処理も、動いているのか止まっているのか分からないくらい遅くなります。
100セル以内くらいなら繰り返しコピペしても問題はなさそうです。

セルの読み書きは最小限

セルの読み書きはシングルスレッドになるので激遅です。列や行の挿入もだめです。どうしてもセルの読み書きをしたい場合は、
コピペではなくて、VBAにてrange(“xxx”).value= range(“yyy”).valueとすれば、多少は良い気がします。とにかくVBAでコピペは使わないのが無難です。

高速化で参考になったページ
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

フィルタしたデータの削除は並び替えてから

100万行のうち条件に該当するデータだけは消したい。そんなとき、オートフィルタで条件を入れて、削除対象のリストが出てきたところで行を選んでまとめて削除。とやりがちですが、それは非常に遅いので、抽出したデータに削除フラグを立てる。削除フラグ>昇順などで並び替え、削除する行を連続させてから消しましょう。

データを取ってきたいときは、関数で取得する

たとえば、OCR認識した3,000バイトくらいの文字列のなかにXXという単語が何回出てきたか。をカウントするときは、= IF( (LEN($E$1 ) – LEN(SUBSTITUTE($E$1,A2, “”)))/LEN(A2)=0,””, (LEN($E$1) – LEN(SUBSTITUTE($E$1, A2, “”)))/LEN(A2) ) という関数を使いましたが、これは100万件並べても速いです。

オートフィルタ(フィルタオプションAdvancedFilter)も速いです。
オートフィルタでSQLと全く同じ処理ができるので、100万件以内のバッチ処理ならばSQLやアクセスを使う必要はないでしょう。
遅いExcelの代名詞であるVLOOKUPなどもセル操作よりは高速であり並列処理できるので、マルチコア環境なら使って良いです。

再計算もしていないのに、もたもた遅くなる場合

このような時はシートを細かく分割していくと解決することがあります。シートごとにメモリの管理が別っぽいため。
Excelが遅い原因は99%がセルの読み書きをシングルスレッドで、もたもた処理していることにあると思います。→このあたりの内部処理は10年くらい前とロジックが変わっていない様子?

高速な文字列検索

セル範囲に文字列があるか、いくつかあるかを高速に検索

ニュース記事3万件×Wikipediaの単語200万件=600億セル(!)のマトリックスを作って、ニュース記事中に使われている単語と使われていない単語に分けたい。自然言語処理用の辞書を作りたい。という疎な行列を処理する場合。

COUNTIF+ワイルドカードは非常に遅いのでMATCH+ワイルドカードを使う(結果は同じではない)
=MATCH(“*”&A2&”*”,B:B,0)
MATCHだと記事中に単語が何回出てきても有無を判定するだけ(見つかった場所が返される)だが、先にそれをして、一度も使われていない単語を削除し、単語数(計算量)を減らしてから細かい処理をやるべし
COUNTIFだと「何件の記事で使われている」という頻度も出してくれる。MATCHはCOUNTIFに比べて体感では10倍くらい?速い
MATCHだと30万件×3万件=90億の処理でも30分(by Core i7 12LP)くらい?

行列処理の高速化

行列のデータ操作に使うコマンド

 

高速なハードウェア

多コア vs 高速なメモリ はどちらが高速なのか?64コアとか意味があるのか?
というのは、研究している人が少なくて情報がなかったのでやってみた。

CPUはSingle Thread性能の良いものを選ぶべきと思う。シングルスレッドとメモリが遅いEPYCで多コア化してもあまり速くならなかった。すべての処理においてオーバークロックメモリ+高速シングルスレッドの勝ち。
前述の「=MATCH(“*”&A2&”*”,B:B,0)」を検索範囲4万セル×70万件=280億セル という処理では、
・開始10分後くらいにEPYC 7281*2が進捗4%に対して、Core i9 9900Kは13%
・開始30分後くらいにEPYC 7281*2が進捗11%に対して、Core i9 9900Kは35%
だったので、3倍以上高速でした。これは、おそらくCPUではなくて高速なオーバークロックメモリが高速化に寄与していると思う。

多コア

AMD EPYC 7281*2のような多コア(64~128LP)でメモリアクセスが遅い(DDR4 2133~2667)
サーバー機はメモリアクセスは速くないのにECC Registeredなので高い。
EPYCなのでSingle Threadも遅い。この構成は失敗。Thread Ripperで64LPを実現した方が良い。

高速なメモリ

高速なメモリはWindowsが全体的に高速化するので目に見えて速くなる。おすすめ。
Intel Core i9-9900K Desktop Processor 8 Cores up to 5.0 GHz Turbo unlocked LGA1151 300 Series 95W
G.SKILL Trident Z Royal Series 16GB (2 x 8GB) 288-Pin RGB DDR4 SDRAM DDR4 4600
メモリは偶数個にして2個より4個(Dual Channel,Quad Channel)とたくさん実装した方がRAID0の原理で高速化する様子。

自然言語処理

Wikipediaの見出し語が約200万語

Wikipediaを辞書にして単語の出現度数をカウントしようとすると、必ず突き当たる壁が、たとえば「ステート・ストリート」のような会社名があったときに、ステートとストリートとステート・ストリートの3つが反応してしまうこと。
手間暇掛けるならば、文字数の多い順に含まれているかいないかを判定して、含まれていたら当該文字列を消す。みたいな処理を繰り返していけば良いが面倒。

京都大学のJUMANに約3万語

http://nlp.ist.i.kyoto-u.ac.jp/index.php?JUMAN

このあたりを使わせてもらうと辞書が作れる。Wikipediaの見出しを金融ニュース記事から検索して使われている語(約8千語)を抽出して、さらにその中からJUMANに掲載されている一般的な日本語を差し引くと金融分野のみで使われる用語辞書ができる。というような使い方。
実際には、どのような文章にも含まれる単語、たとえば、「今年」「関連」のような語が大量に含まれてくるので、それをTF-IDFの原理で重要度別に分類してフィルタ、削除みたいなことが必要になるが面倒。

新機能SPILL

これはOffice 365というサブスクに加入し、さらにOffice Insiderというのに登録しないと使えない機能。
パッケージ売りのOffice 2019には搭載されていない関数。
GoogleスプレッドシートでできることをExcelにも取り入れた感じ。ぜひ使いたいのだがExcel使うのにサブスクに金を払うことには違和感があり検討中。

FILTER 関数
RANDARRAY 関数
SEQUENCE 関数
SINGLE 関数
SORT 関数
SORTBY 関数
UNIQUE 関数

バグや不都合

Excelは非常によくできていて、バグもほとんどなく優秀なツールです。むしろ、バグを見つけるのは簡単ではなく、操作の間違いであることのほうが多いですが、気付いた点をまとめています。

ピボットテーブルに集計フィールドを追加してデータ更新をすると書式が勝手に変えられてしまう

ピボットテーブルオプション>更新時にセル書式を保持する で解決します。これはデフォルトでonにしてくれてもいいのでは?ということで、バグではないですが、不都合です。

何もしていないのに変更を保存しますか?と聞かれる

これは=Now()や=Today()など開くたびに戻り値が変わる変数を使っていることが原因らしい(Excel Hacks第2版)