Apache poi + JDBCでエビデンス取得生成ツールを作ってハマった話

今回作ったツールの概要(動き)

実行したいSQLを読み込む

DBにアクセスして実行

エクセルを生成

データを吐き出す(書き込む)

エクセルファイルとDBセッションを閉じる

といった感じのツールが作りたかった(仕事で楽できそうだし)

※動作検証(性能確認)
テスト用テーブルを用意し、100カラム(各カラムを文字型20文字で定義し、乱数で20桁までの数値文字列を格納)×約10万行を準備

ハマった点<DBからの取得件数が多くなると固まる>
実行したときの画面(出力時のxx件目を標準出力している)

だいたい2.7~2.8万で固まる もしくは java.lang.OutOfMemoryError: Java heap space で落ちる

対策①
ResurtSetがメモリを圧迫しているのだと予想し、実装を件数が多いときのみ単純なSELECTからページングするように実行時SQL文字列に小細工するよう改修(2万くらいで切りながらSELECTする感じ)

結果(変わらんやんけ!!!)

対策②
よく考えてみるとそもそも動き的にデータ取得したときにResurtSetのメモリ容量確定しているのでは? つまり、エクセル出力時に出力行数多すぎてメモリを食いつぶしている あっ…

調べてみるとSXSSFWorkbookクラスを使うとメモリ容量を指定して使えるらしい(100行分で指定すると100行処理が終わるとメモリ破棄する感じ)。

ちなみに今回使っていたのはXSSFWorkbookクラス…
SXSSFWorkbookを使用するように改修してみる。

なんやコレサックサクに早いやんけ!

改修前のプログラム実行時のリソースモニタ

CPU使用率がほぼ100%(待機時は8~10%)
メモリは裏でVMとか動かしてるのでアレな感じですが…

改修後のプログラム実行時のリソースモニタ

CPU使用率が驚くほど違いますね(実行時に一瞬25%になりましたが15~20%の間をウロウロしながら終了しました。)
メモリもザックリしか見れませんが明らかに差がありますね。

結論
Apache poiで大量データを捌くならSXSSFWorkbookを使おう
SXSSFWorkbookには欠点がありautoSizeColumnが使えない(困った…)

今回使用したライブラリ他
Apache poi 4.1.0
☆画面に映ってないもの
ojdbc8
oracle 12c R2(vBox + Docker + oracleDB)

参考ページ
http://poi.apache.org/components/spreadsheet/how-to.html#sxssf

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です