poiでwb.write(out)する際の処理が非常に重い

Question

お世話になっております。

poiでエクセルに書き込み(wb.write(out))を行った際の処理が
非常に重く、30秒以上かかっております。

コードは以下になりますが、高速化させることはできませんでしょうか。

protected void doDownload(HttpServletResponse response) {


        String filePath = getServletContext().getRealPath("/WEB-INF/excel/donwload.xlsx");

        ZipSecureFile.setMinInflateRatio(0);

        try {
	        wb = new XSSFWorkbook(filePath);
        } catch (IllegalStateException e) {
        	System.out.println("テンプレートのエクセル読み込み時に例外が発生しました。エクセルファイル名が間違っている可能性があります。");
    	} catch (IOException e) {
			e.printStackTrace();
	    }
        sheet = wb.getSheet(getSheetName());

        response.setContentType("application/octet-stream");
        String fileName = null;
        try {
            fileName = new String(getExcelFileName().getBytes("Windows-31J"), "ISO-8859-1");
        } catch (UnsupportedEncodingException e1) {
            e1.printStackTrace();
        }
        response.setHeader("Content-Disposition", "attachments; filename=\"" + fileName + "\"");
        response.setHeader("Cache-Control", "public");
        response.setHeader("Pragma", "public");

        try {
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            wb.write(out);
        } catch (IOException e) {
                              e.printstactrace();
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                              e.printstactrace();
            }
        }
    }

追記

（１）ボトルネック部分ですが、上記コードのwb.write(out)の部分となります。それ以外は、時間はかかっていない状況となります。
なお、wb.write(out)はローカルにあるファイルをローカルにダイアログでダウンロードで返しています。

（２）ローカルでホストはMacゲストがWin7の64bit メモリ8Gで動作させていてます。

（３）jvmのヒープヒープサイズは以下に設定しております。
-Xmx2g
-Xms2048m
-Xmx4098m

（４）該当のエクセルファイルは3M程度のエクセルを読み込みこむ形となります。
そのファイルに対して、データを横10列のものもあれば、150列のものもあり、行数は、数千行～数万行を、書き込む形となります。
当該重いのは、行数は、数千行ですが、横が１５０列程度あります。
数式が多く設定されていまして、また細部にこだわる仕様のため、書式も多く設定されております。

追記2
CPU/memory監視状況を添付します。
メモリなど多めに割り当てているため、
マシンに負荷はかかっていないですが、
エクセルへの書き込みとそのダウンロードが非常に重い状況です。

Comments

[eripong]

操作するコードがない様に見えますが、高速化の検討に必要と思うので、省略しているのであれば、できるかぎり提示してください。また、30秒はかなり長いですが、download.xlsxのファイルサイズはどのくらいですか？
JVMのヒープサイズは？ハードのスペックはどのくらいでしょうか？
ネットワーク的には離れていますか？あるいはローカルマシン上？

Answer 1

皆様

ご回答いただき誠にありがとうございます。

ボトルネック部分について、原因がわかりましたので共有させていただきます。

原因は、Virtualboxの共有フォルダが原因でした。
何年も前に同様の経験がありましたが、失念しておりました。

ふと、Eclipseを立ち上げた際、ワークスペースがホスト側のフォルダになっており気づくのが遅くなりました。。

ホスト側でなく、ゲストで動かしているのでゲスト側のワークスペースにしたところ、大幅に改善されました。

恐縮です。

Comments

[eripong]

改善されて良かったです。後学のために伺いたいのですが、poiの内部ではファイルアクセスを頻繁に行っていると言うことでしょうか？あるいはクラスロード周りでのファイルアクセスが起きていた？分かれば教えていただけませんか？

[退会済みユーザー]

お世話になっております。
poi内部の解析はしなかったのですが、起きた事象から推測ではありますが、
データをExcelに貼り付けた後、書込(wb.write(out))に行った際のファイルアクセスが
ゲストからホスト側のフォルダへの書込となっており、
その場合、以下の記事等でも書かれているように、
パフォーマンスが大幅に低下するようです。
http://webradar.info/2014/03/06/slowly-rails-in-vagrant/
今回は、それが原因でした。

[eripong]

返信ありがとうございます。

wb.write(out)はレスポンスへの書き込みなので、ネットワークアクセスであって、ファイルに直接出力しているのではないという認識でした。どちらかというと、ブラウザがファイルに書き込んでいるのかと。ブラウザのファイル書き込みを待つとこういう見え方になるのかも？

とはいえ、解析に時間をかけないと分からなそうなので、ひとまず了解です。

[退会済みユーザー]

wb.write(out)の処理に必要なクラスのロード等でホスト側にアクセスしていたのかもしれませんね。

[eripong]

そうすると、2回目以降は早いのか？とか疑問が出てきますね。興味深い状況ではあります。

Answer 2

いくつか気になる点がありましたので、それも合わせて。

ZipSecureFile.setMinInflateRatio(0);
デフォルト値は1.0ですが、0にした理由はありますでしょうか。

POIのバージョンも合わせて記載しておきましょう。
というのもPOIのバージョンによって推奨されない実装方法があります。

以下はpoi-ooxml-3.14版を使ったサーブレット＋エクセルの読み込み例です。

String filePath = getServletContext().getRealPath("/WEB-INF/download.xlsx");
		
InputStream inp = new FileInputStream(filePath);
Workbook wb;
try {
	wb = WorkbookFactory.create(inp);
} catch (EncryptedDocumentException e) {
	throw new ServletException(e);
} catch (InvalidFormatException e) {
	throw new ServletException(e);
}

記載してくださったサーブレットのソースでは、POIを使って読み込み→そのままサーブレットから出力していますので、30分もかかる場合は以下の懸念があります。

• 単純にファイルサイズが巨大(数百メガ～)なため、ファイルの読み込みに時間がかかる
• セルに記載しているデータが非常に多い

ファイルサイズが大きいものの場合は、もし可能ならシートを分けて処理するなどの方法がありますが、要件次第でしょうか。

Comments

[退会済みユーザー]

ご回答ありがとうございます。
poiのバージョンは3.14です。

```
ZipSecureFile.setMinInflateRatio(0);
```
上記は、以下エラーメッセージが出まして、「zipbomが見つかった時に拡張が云々」ということで、あまりよくわかりませんないようでしたが、ZipSecureFile.setMinInflateRatioでlimitを調整できるということで、0にしてみましたら、エラー発生せず動作した為いれています。

Caused by: java.io.IOException: Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data. This may indicate that the file is used to inflate memory usage and thus could pose a security risk. You can adjust this limit via ZipSecureFile.setMinInflateRatio() if you need to work with files which exceed this limit. Counter: 827392, cis.counter: 8192, ratio: 0.009900990099009901Limits: MIN_INFLATE_RATIO: 0.01

ちなみに、出力時間についてですが、30分ではなく、30秒となります。
その程度は仕方ない気がしてきました。

[A-pZ]

なるほど詳細にありがとうございます。時間については失礼しました。
ファイルサイズによっては多少時間はかかりますね。特にセルに書かれているデータ量が多い場合や、罫線が多い場合も読み込みに時間がかかります。

Answer 3

ダメ元ですが、
アプリサーバーのJVMのメモリーに余裕があるなら、
BufferedOutputStreamのバッファーサイズを増やしてみるとか。
おそらくExcelの書き出し側で遅いので改善は見込めないと思いますが。

OutputStream out = new BufferedOutputStream(response.getOutputStream(), 65536); // デフォルトは8192

...

本格的に調査するなら、プロファイリングを行って、
特に遅い箇所（ボトルネック）を突き止める必要があります。
JDKに Java VisualVM (jvisualvm)というプロファイラーが標準搭載されていますので、
それを使ってみるのも手です。

ただプロファイリングは難易度が高いですし、時間もかかります。

...

あとは代替策を取るという手もあります。

Excelを作るのとダウンロードを分けるとか。
これはこれで仕組みを作るのはちょっと手間ですけどね。

Comments

[退会済みユーザー]

ご回答ありがとうございます。
jvisualvm　というのがあるのですね。勉強になります。
こちらを使って確認したところ、ひっ迫した状況ではありませんでした。
また、バッファサイズの変更で変化があらわれませんでした。
Poiのwriteするときがどうしても重いようです。

[eripong]

質問追記内容を見ました。GC以外でCPU50%使っているようなので、純粋にExcelファイル生成などの計算で時間がかかっているようですね。argiusさんの言うとおり、プロファイルしてボトルネック特定して改善するか、Excelを作るのとダウンロードを分けるののどちらかかなと思います。

[eripong]

プロファイルするなら、一時的にでもTomcatのJavaのバージョンを上げた方が良いかも知れません。Visual VMでプロファイラタブが表示されるはずです。

[argius]

keetonさん

Excelデータの詳細とVisualVMの画像を追記していただきありがとうございます。
サーブレットのダウンロードに関係なく、単純にExcelデータが大きいので処理に時間がかかってしまっているような気がしますね。
POIでは限界があるのかも知れません。

eripongさん

コメントありがとうございます。
やはりそうなりますかね。

Answer 4

提示されたソースを見る限り、改善の余地はないと思われます。
POIのソースを入手し、ボトルネックを調べるか、Excelファイル自体を最適化するかでしょうか。