動的な列構成の変更 (Dynamic columns in the report)

OTNに表の列構成を動的に変更させる方法が質問されていました。
Thread: Hiding Column keeping the table size constant
https://forums.oracle.com/forums/thread.jspa?threadID=2332900

スレッドでは解決に至っていませんが、列数（表のパターン）があらかじめ判明している場合には、行または表そのものを切り替える方式が効果的です。以下に例を示します。

出力例は以下の通りです。

Figure 1: output

1つ目のサンプルでは、deptnoのグループ毎に表の構造をif分岐で切り替えます。
2つ目のサンプルでは、行ごとに列の構造を切り替えます。

タグの設定内容は以下の通りです。

Figure 2: Settings

テンプレートおよびデータはここからダウンロードできます。

---

[Summary]
Thread: Hiding Column keeping the table size constant
https://forums.oracle.com/forums/thread.jspa?threadID=2332900

The sample above explains how to switch the table structure.  The first example shows how to switch the table structure.  The second example switches the row structure in each row.
The sample template is available here.

ラウンドトリップタイム (Round-Trip Time; RTT)

海外拠点やクラウド事業者、災対サイトなど、ラウンドトリップタイム（RTT） が長い場所にあるDBから（やむを得ず）直接データを取得しなければならないケースが増えてきています。

ほとんどのDBクライアント用ドライバは、結果セットを分割して取得する仕様を採用しています。JDBCを例にとると、デフォルトでは10行ずつデータを取得（フェッチ）します。したがって、結果セットが25行ある場合、実際にはAPサーバとDBサーバの間ではデータが3回に分けて転送され、2回分のRTTが余分に経過します。

帳票は一般的に扱う行数が多い傾向があります。LPAR間などであれば問題が顕在化しないこともありますが、以下のいずれかに当てはまる場合、フェッチサイズの調整は検討に値します。

• DBサーバとの間のRTTが長い
• 取得する行数が極端に多い

また、プロジェクトで使用するフレームワークや共通部品は、場面に応じたフェッチサイズ変更を考慮した設計をお勧めします。

---

[Summary]

You can reduce the database round-trip by setting appropriate fetch size.

Apache POI によるエクセル帳票出力の並列実行 その2 (Apache POI parallel processing, Part 2)

前回の並列実行の検証では、3スレッドまでは高い性能向上を示した後、伸び率が低下することが確認されました。
今回は、この時のOS統計を確認します。

【CPU】
CPU使用率の推移は以下の通りです。
※なお、検証環境の物理コア数は4です。

Figure 3: CPU usage

設定したスレッド数以上のコアが使用されていることが確認できます。これはJVM自身の挙動に関わるオーバーヘッドであり、これらを含めたスレッド数が物理コア数を超えたことで4スレッド時点で性能の伸び率が鈍化し始めたと想定されます。
※本記事ではこれ以上の調査は行いません。
過去にCOM経由でエクセルを操作した際の値と比較すると、CPU使用率が非常に低く抑えられています。性能の伸び率の高さは、このCPU使用率の余裕から生まれていると考えられます。

プロセッサ・キュー（Processor Queue Length）の値は6スレッドから目立ち始め、7スレッド以降は過負荷であることを示しています。

Figure　4: Processor Queue Length

参考まで、コンテキスト・スイッチ（Context Switches/sec）の発生も確認します。COM経由でExcelを操作した際の値と比較すると、10分の1以下で推移しています。

Figure 5: Context Switches / sec

【メモリ】
スレッド数の増加に伴って開きメモリが減少しますが、5GB以上の余裕があります。
グラフは割愛しますが、ページングも発生しておらず、問題は見られません。

Figure 6: Available Memory (MB)

【ディスク】
ディスクへの負荷はそれほど高くありません。グラフは割愛しますが、キューの待機も発生していません。

Figure 7: Disk usage

【GC】
今回はヒープを十分に確保（1GB）したため、Full GCは発生していません。参考まで、以下にJVMの推移を示します。

Figure 8: GC

※NEWについては改善の可能性もありますが、今回の検証ではこれ以上のチューニングは行わないこととします。

【結果】
以前の検証でエクセルによる帳票出力(COM経由での操作)を行ったときと同様、CPUの処理容量に依存しやすい傾向が確認されました。
※参照：「リンク」

COM経由でエクセルを操作する場合と比較すると、単位時間当たりの出力性能はApache POIが大幅に上回っています。
また、Apache POIを使用する場合はサーバ側のOSを選ばない点、およびサーバ側にエクセルのライセンスが不要である点も優位であるといえます。
しかしながら、Apache POI（またはその他のライブラリ）を使用する際には機能制限に留意する必要があります。現行バージョンの3.7ではXSLX形式の条件付書式に対応していない（次期3.8で対応予定）等、ユーザ要件を満たせない場合があります。エクセルのもつ機能・表現力を十分に発揮させたい場合には、これらの機能制限が不利となります。

帳票要件の複雑さと処理性能や実装環境を考慮し、Excelによる実装とApache POI等の外部ライブラリでの実装を使い分けることをお勧めします。
管理面からは「実装方式を一本化する」という方針が魅力的に見えることがありますが、実際には機能制限の回避や性能問題の解消などに不要な出費を強いる原因となりがちです。技術者の確保も容易な分野ですので、相当の理由がない限り、一本化は避けることをお勧めします。

---

[Summary]
OS statistics shows that Apache POI file processing is CPU bound.Please see Figure 3 to 7.
For your reference, Figure 8 shows JVM GC statistics.


The advantages of Apache POI:  The performance Apache POI manipulation is much faster than the performance of COM-Excel manipulation.  In addition, you do not need the Excel licence on the server side.
The disadvantage  of Apache POI: Apache POI does not support all the functions that Excel provides.

Apache POI によるエクセル帳票出力の並列実行 その1 (Apache POI parallel processing, Part 1)

前回のサンプルプログラムについて、並列処理で単位時間当たりの処理能力の向上を図り、どの程度リニアに性能が向上するかを検証します。

【検証の方法について】
通常の運用であれば、スケジューラのジョブを並列実行させますが、今回のサンプルではプログラム内で複数スレッドを生成することで検証を行います。
検証環境は「エクセルファイルのバッチ出力 その5」と同様です。
※本記事は帳票出力の並列実行をプログラム内で実装することを推奨しているわけではありません。エミュレートが目的です。

【サンプルソース（呼び出し部）】
サンプルのソースの内、呼び出し側を以下の様に修正します。
並列度はソース内のNUM_THREADSで指定し、ExecutorServiceを使用して実行スレッド数と同数のスレッドプールを生成します。
並列度は１から8（論理コア数）まで変化させ、各スレッドで1000ファイルを出力するまでに経過した時間を計測します。

List 1:

public class Test2 {
  private static final int NUM_THREADS = 1;

  public static void main(String[] args) {
    System.out.println(new Date());
    
      ExecutorService executor = Executors.newFixedThreadPool(NUM_THREADS);
      
      for(int j=0; j < NUM_THREADS; j++){
      executor.execute(new Runnable(){
        public void run() {
        long timeStarted = System.currentTimeMillis();
          try {
          Ora2Excel o2x = new Ora2Excel();
          
          o2x.openDb("scott", "tiger", "jdbc:oracle:thin:@windows2003srv:1521:orcl");
          
          for (int i = 0; i <1000; i++) {
            o2x.openBook("c:\\template.xlsx");
            o2x.extract("Sheet1", "select deptno, empno, ename, job, mgr, hiredate, sal, comm from emp order by deptno, empno");
            o2x.saveBook("c:\\temp\\result_" + Thread.currentThread().getId() + "_" +  i + ".xlsx");
            o2x.closeBook();
          }
          
          o2x.closeDb();
          System.out.println("finish: " + Thread.currentThread().getId() + "=" + (System.currentTimeMillis()- timeStarted));
          
        } catch (Exception e) {
          e.printStackTrace();
        }
        }
      });
    }
      executor.shutdown();
  }   
}

【サンプルソース（本体部）】
本体部のソースに変更はありません。ソースは「Apache POI によるエクセル帳票出力の並列実行 その1」を参照してください。

【結果】
出力性能の結果は以下の通りとなりました。以下のグラフは、横軸が多重度（スレッド数）を表し、縦軸が各スレッドの処理完了時間（秒）を表します。1多重から３多重までは処理時間はほぼ変動せず、約35秒で推移していますが、4多重以降は処理時間が増加していることが確認できます。

Figure 1: Elapsed Time

上記の結果を、1秒当たりの出力ファイル数に換算した結果を以下に示します。縦軸は1秒当たりの出力ファイル数です。

Figure 2: Performance Result

1多重での出力性能は1秒間に29ファイルです。2多重及び3多重ではほぼ100%の性能向上が確認できます。
4多重（物理コア数に同じ）でも89%とまずまずの成績です。
5多重から先は性能が頭打ちになり、8多重では61%の性能向上となっています。


C#からCOM経由でExcelを操作した場合（「エクセル帳票出力の並列実行 その1」を参照）と比較しても性能向上の割合が優れています。

次回はOS統計を確認します。

---

[Summary]

In this post, we'll see the performance of Apache POI with parallel (multi thread) processing.

[Environment]

Please refer to Excel file processing, Part 5.

[Test source]

In the ordinary batch system, you may run programs by job scheduler parallel.  In this test, I implement multi thread processing within the sample program.  Please refer List 1.

[Source (main part)]

No changes have been made.  Please refer to Export to Excel file via Apache POI, Part 1 for the source code.

[Result]

In Figure 1 and Figure 2, the bottom axis represents the number of threads.

Figure 1 shows the elapsed time for each thread.

Figure 2 shows the output performance.  Single thread performance is 29 files per sec.  Until three thread, it keeps almost 100% performance increase.

In the next post, we read the OS statistics.

Apache POI によるエクセルファイルの出力 その2 (Export to Excel file via Apache POI, Part 2)

前回のサンプルプログラムの性能を確認します。

CPU性能等の検証環境については「エクセルファイルのバッチ出力 その5」を参照してください。


【呼び出し側プログラムの修正】
テストプログラム側を以下の様に修正し、100ファイルを出力する際の処理時間を計測します。


List 3:

public class Test1 {

  public static void main(String[] args) throws Exception {
    long timeStarted = System.currentTimeMillis();

    Ora2Excel o2x = new Ora2Excel();
    
    o2x.openDb("scott", "tiger", "jdbc:oracle:thin:@windows2003srv:1521:orcl");
    
    for (int i = 0; i <100; i++) {
      long lapTime = System.currentTimeMillis();
      o2x.openBook("c:\\template.xlsx");
      System.out.println("[file open] " + (System.currentTimeMillis()- lapTime));
      
      lapTime = System.currentTimeMillis();
      o2x.extract("Sheet1", "select deptno, empno, ename, job, mgr, hiredate, sal, comm from emp order by deptno, empno");
      System.out.println("[extract] " + (System.currentTimeMillis()- lapTime));
      
      lapTime = System.currentTimeMillis();
      o2x.saveBook("c:\\temp\\result_" + i + ".xlsx");
      System.out.println("[save] " + (System.currentTimeMillis()- lapTime));
      
      lapTime = System.currentTimeMillis();
      o2x.closeBook();
      System.out.println("[close] " + (System.currentTimeMillis()- lapTime));
    }
    
    o2x.closeDb();
    System.out.println("[total] " + (System.currentTimeMillis()- timeStarted));
  }
}

上記の処理結果ログを集計し、1ファイルあたりの平均処理時間を算出した結果は以下の通りです。最も長い処理イベントはテンプレートファイルを開く部分(file open)であることがわかります。

Figure 4: Events

上記の結果は１００ファイルを出力した際の平均値ですが、実際には、１ファイル目と２ファイル目以降の処理時間は大きく異なります。以下のグラフは縦軸が各イベントが１ファイル毎に要する時間、横軸が処理ファイルの順番を表しています。２ファイル目以降の処理時間が大きく低減していることが分かります。

Figure 5: Elapsed time per file

１ファイル目ではテンプレートファイルを開くのに1.12秒、全体（1ファイルあたり）で約1.8秒要していましたが、2ファイル目以降、順次0.3秒程度まで低減し、1ファイルあたりの処理時間も平均で0.07秒まで低減しています。
単純に換算すると秒間の出力性能は約14ファイルとなりますので、1万ファイルを出力する場合はおよそ12分弱と推計されます。（2012-01-11 削除）

次回は並列実行した場合の検証を行います。

【2012-01-11追記】
今回の検証ではJVMの起動（および初回のロード）に伴うオーバーヘッドが経過時間に含まれてしまっています。このため、たとえば起動済みJVMで処理を行った場合や、出力するファイル数を増加させた上で平均をとった場合には、性能はより向上します。手元で改めて1000ファイルを出力した場合、1ファイルあたりの出力時間は約27.5ファイルとなりました。

---

[Summary]
The sample program shown in List 3 outputs 100 excel files (The original source is in the last post).


Figure 4 shows that the longest event is opening the template file - it takes about 35 millisecond per a file.
Figure 5, the left axis shows the elapsed time for each event per a file. The bottom axis shows the sequence number of the file produced. This graph shows that the first file takes much longer - around 1.8 second - than the following files.



[January, 11, 2012: append]
The elapsed time includes JVM start up overhead.  This overhead had reduced the performance.  The more files, the Additional test result: 36.27 sec. to produce 1000 files - 27.5 files per sec.