SQLで小計と合計を取得する:補足 (SQL: Subtotal and Grand Total: Appendix)

前回の記事で、処理性能の検証結果に「非効率なSQLの例」を載せ忘れていましたので掲載します。
また、group by および group by rollup()の例も併せて再掲します。

List 8:

********************************************************************************

select *

  from (

       select deptno, job, sum(sal) sum_sal

         from emp2

        group by deptno, job

       union

       select deptno, null as job, sum(sal) sum_sal

         from emp2

        group by deptno

       union

       select null as deptno, null as job, sum(sal) sum_sal

         from emp2

       )

 order by deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.01          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      1.10       1.41      25695      50853          0          13

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      1.10       1.43      25695      50853          0          13

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

     13  SORT ORDER BY (cr=50853 pr=25695 pw=0 time=0 us cost=1083 size=480 card=15)

     13   VIEW  (cr=50853 pr=25695 pw=0 time=120 us cost=1082 size=480 card=15)

     13    SORT UNIQUE (cr=50853 pr=25695 pw=0 time=36 us cost=1082 size=190 card=15)

     13     UNION-ALL  (cr=50853 pr=25695 pw=0 time=192 us)

      9      HASH GROUP BY (cr=16951 pr=8565 pw=0 time=32 us cost=248 size=165 card=11)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=2393598 us cost=239 size=1800000 card=120000)

      3      HASH GROUP BY (cr=16951 pr=8565 pw=0 time=202 us cost=248 size=21 card=3)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=2320510 us cost=239 size=840000 card=120000)

      1      SORT AGGREGATE (cr=16951 pr=8565 pw=0 time=0 us cost=587 size=4 card=1)

1200000       TABLE ACCESS FULL EMP2 (cr=16951 pr=8565 pw=0 time=1736062 us cost=239 size=480000 card=120000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  direct path read                                8        0.00          0.01

  asynch descriptor resize                        5        0.00          0.00

  SQL*Net message from client                     2        0.01          0.01

********************************************************************************

select deptno, job, sum(sal) sum_sal

  from emp2

 group by deptno, job

 order by

       deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.54       0.53         15       8580          0           9

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.54       0.53         15       8580          0           9

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

      9  SORT GROUP BY (cr=8580 pr=15 pw=0 time=0 us cost=284 size=135 card=9)

1200000   TABLE ACCESS FULL EMP2 (cr=8580 pr=15 pw=0 time=2297470 us cost=245 size=18000000 card=1200000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                        15        0.00          0.00

  SQL*Net message from client                     2        0.00          0.00

********************************************************************************

select deptno, job, sum(sal) sum_sal

  from emp2

 group by rollup (deptno, job)

 order by

       deptno, job

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.54       0.56         17       8580          0          13

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4      0.54       0.56         17       8580          0          13

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 86  

Rows     Row Source Operation

-------  ---------------------------------------------------

     13  SORT GROUP BY ROLLUP (cr=8580 pr=17 pw=0 time=0 us cost=284 size=195 card=13)

1200000   TABLE ACCESS FULL EMP2 (cr=8580 pr=17 pw=0 time=2089726 us cost=245 size=18000000 card=1200000)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  db file sequential read                        17        0.00          0.00

  SQL*Net message from client                     2        0.01          0.01

---

[Summary]
I forgot to put the trece of Bad Example in the last post.
Here List 8 shows the results of three SQL - bad example, group by and group by rollup.

SQLで小計と合計を取得する (SQL: Subtotal and Grand Total)

帳票を1枚ずつカスタマイズして作成する場合には問題になりませんが、帳票ツールや部品によっては、出力内容を1つのデータセット（またはSQL）として受け渡す必要がある場合があります。
これにさらに「小計と合計を出力」という要件が加わり、非効率なSQLの記述を余儀なくされているケースがあります。

非効率なSQLの例と結果、および実行計画を以下に示します。この例では、集計値を表示するために同一表への検索が３回実行されます。

List 1:

select * from ( select deptno, job, sum(sal) sum_sal from emp group by deptno, job union select deptno, null as job, sum(sal) sum_sal from emp group by deptno union select null as deptno, null as job, sum(sal) sum_sal from emp ) order by deptno, job; Rows Row Source Operation ------- --------------------------------------------------- 13 SORT ORDER BY (cr=21 pr=0 pw=0 time=0 us cost=15 size=480 card=15) 13 VIEW (cr=21 pr=0 pw=0 time=12 us cost=14 size=480 card=15) 13 SORT UNIQUE (cr=21 pr=0 pw=0 time=12 us cost=14 size=190 card=15) 13 UNION-ALL (cr=21 pr=0 pw=0 time=24 us) 9 HASH GROUP BY (cr=7 pr=0 pw=0 time=8 us cost=5 size=165 card=11) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=11 us cost=3 size=180 card=12) 3 HASH GROUP BY (cr=7 pr=0 pw=0 time=8 us cost=5 size=21 card=3) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=11 us cost=3 size=84 card=12) 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us cost=4 size=4 card=1) 12 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=48 card=12) 

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

Figure 1: Subtotal and Grand Total

今回はOracleのSQL構文で小計と合計をより少ない負荷で出力する手法を確認します。


【データ】
データはscott.emp表を使用します。

Figure 2: scott.emp

【例】
基本となる構文は非常にシンプルで、group by句にrollupを追加するだけです。
rollupに列を指定することで、指定した列を単位とする集計を得ることができます。
集計の出力される行は、集計の単位となる列の値がNULLで出力されます。

例と出力結果は以下の通りです。この例では、deptno列の集計、つまり合計が出力されます。合計はdeptno列がNULLで出力されています。

List 2:

select deptno, sum(sal) sum_sal from emp group by rollup (deptno) order by deptno; 

Figure 3

以下の例ではjob列の集計（小計）と、deptno列の集計（合計）が出力されます。冒頭の例と同じ結果を得ることができます。
job列毎の小計はjob列がNULLで出力されています。また、合計はdeptno, job列ともにNULLで出力されています。

List 3:

select deptno, job, sum(sal) sum_sal

  from emp

 group by rollup (deptno, job)

 order by

       deptno, job; 

Figure 4

【集計行の識別】
rollupの指定で生成された行を識別するために、grouping関数が提供されています。
grouping関数は、集計行の場合に数値型の１、それ以外の行ではゼロを戻します。
上述のSQLを以下のように修正します。集計行の該当する列に１が出力されていることが確認できます。

List 4:

select deptno, job, sum(sal) sum_sal, grouping(deptno), grouping(job)

  from emp
 group by rollup (deptno, job)
 order by
       deptno, job;

Figure 5: Usage of GROUPING function

grouping関数とdecodeを組み合わせた例を以下に示します。
この例では、集計行に「Subtotal」および「Grand Total」の文字列を表示します。

List 5:

select deptno,

       case
         when grouping(deptno) = 1 then
              '[Grand Total]'
         when grouping(job) = 1 then
              '[Sub Total]'
         else job
       end job,

       sum(sal) sum_sal

  from emp

 group by rollup (deptno, job)

 order by

       deptno, job;

Figure 6: DECODE with GROUPING

【性能】
rollupの使用による性能への影響を確認します。
120万件のデータに対して、通常のgroup byとgroup by rollup()の問い合わせをかけた結果のトレースは以下の通りです。

List 6:

******************************************************************************** select deptno, job, sum(sal) sum_sal from emp2 group by deptno, job order by deptno, job call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.54 0.53 15 8580 0 9 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.54 0.53 15 8580 0 9 ... Rows Row Source Operation ------- --------------------------------------------------- 9 SORT GROUP BY (cr=8580 pr=15 pw=0 time=0 us cost=284 size=135 card=9) 1200000 TABLE ACCESS FULL EMP2 (cr=8580 pr=15 pw=0 time=2297470 us cost=245 size=18000000 card=1200000) ******************************************************************************** select deptno, job, sum(sal) sum_sal from emp2 group by rollup (deptno, job) order by deptno, job call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.54 0.56 17 8580 0 13 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.54 0.56 17 8580 0 13 ... Rows Row Source Operation ------- --------------------------------------------------- 13 SORT GROUP BY ROLLUP (cr=8580 pr=17 pw=0 time=0 us cost=284 size=195 card=13) 1200000 TABLE ACCESS FULL EMP2 (cr=8580 pr=17 pw=0 time=2089726 us cost=245 size=18000000 card=1200000)

集計処理による負荷の増大はトレースには明確には表れていません。通常の使用においては、性能への影響はおおむね無視できると考えられます。

なお、サンプルの120万件のデータは以下のスクリプトで生成しました。

List 7:

create table emp2 ( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2), pageno number(10,0), -- page number 1..100000 dummycol number(10,0) -- always 1 );



declare
  cursor c is select * from emp order by deptno, empno;
  i pls_integer;
begin
  for i in 1..100000 loop
    for r in c loop
      insert into emp2 values (r.empno, r.ename, r.job, r.mgr, r.hiredate, r.sal, r.comm, r.deptno, i, 1);
    end loop;
  end loop;
  commit;
end;
/

【編集後記】
当ブログには「Oracle、小計」の検索でたどり着く方が多いのですが、SQLではなくBI Publisherの出力の説明（「小計と合計 (Subtotal and Grand total)」）でガッカリした、という声を複数いただきました。今回の記事が参考になれば幸いです。
また、group byには、この他、クロス集計値を求めるcubeも指定可能です。必要に応じて下記マニュアルを参照してください。
Oracle Database SQL言語リファレンス 11gリリース2（11.2）B56299-02：
http://download.oracle.com/docs/cd/E16338_01/server.112/b56299/statements_10002.htm#i2182483

---

[Summary]
Some times requirement like below forces you to write heavy SQL.

• Requirement: Get the data, Subtotals and the Grand Total in one SQL (or dataset).

Please see List 1 for the bad example.  The SQL executes same query three times.

You can achieve this requirement with group by rollup().

[Example]
Figure 2 shows the original data.
The syntax is very simple and easy to use.  All you need to do is just append rollup after group by.
List 2 and Figure 3 show how to get the summary row as Grand Total.
List 3 and Figure 4 show Subtotal sample with group by rollup (deptno, job).


[grouping function]
Oracle provides grouping function to identify the summary row.
grouping returns 1 on the summary row, and returns zero on the other rows.  Please see List 4 and Figure 5.
List 5 and Figure 6 shows the usage with decode function.


[Performance]
List 6 shows the trace of group by and group by rollup().
There seems few performance overhead.


[Reference]
Oracle Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-03
http://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_10002.htm#i2182483

BI Publisher の Java APIを使用する(Generate PDF with BI Publisher Java API)

Oracle BI Publisherが公開しているAPIは非常に有用です。これらのAPIを使用することで、複雑な出力条件を実装した外部アプリと、BI Publisherのレポート生成エンジン部分を連携させることが可能です。
マニュアルは以下のURLで参照できます。
http://download.oracle.com/docs/cd/E15586_01/fusionapps.1111/e20838/javaapis.htm

残念なことに、BI Publisher 11gではAPIの仕様が変更されており、10gとの互換性が一部失われています。かつ、仕様変更についての情報は提供されていないという不親切な状況が続いています。
今回、10gと同様のコーディングを11gで実装する方法が判明したので紹介します。バージョンは11.1.1.3です。
※11g固有のAPI使用方法については、判明次第、紹介したいと思います。


【事前準備】
マニュアルの「7.2 Prerequisites」に従い、各jarファイルにクラスパスを設定します。マニュアルにはxdocore.jarと記載されていますが、これはxdo-core.jarの誤記です。ファイル名が10gと11gで変わっています。
また、マニュアルには記載がありませんが、xdo-server.jarも併せて必要です。このJARにはデータソースからXMLファイルを出力するためのdataengineクラスが含まれています。

【処理の概要】
処理の概要図はマニュアルに記載されています。
今回は以下の順に処理を行います。
・データソースからデータを抽出し、XMLファイルを生成する。
・RTFテンプレートからXSLファイルを生成する。
・XMLファイルとXSLファイルからPDFファイルを生成する。

【ソース】
最小構成のソースは以下の通りです。
List 1:

import java.sql.DriverManager; import java.sql.Connection; // (*1) import oracle.xdo.dataengine.v1.*; // xdo-server.jar (*2) import oracle.xdo.template.FOProcessor; // xdo-core.jar import oracle.xdo.template.RTFProcessor; // xdo-core.jar public class Sample1 { public static void main(String[] args) throws Exception { // generate data XML file - select from Oracle database. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@dbsrv:1521:orcl", "scott", "tiger"); DataProcessor dataProcessor = new DataProcessor(); dataProcessor.setOutput("c:\\empdata.xml"); dataProcessor.setConnection(con); dataProcessor.setSql("select * from scott.emp order by empno"); dataProcessor.processData(); // generate XSL file with the template (*.RTF) RTFProcessor rtfProcessor = new RTFProcessor("c:\\layout1.rtf"); rtfProcessor.setOutput("c:\\layout1.xsl"); rtfProcessor.process(); // generate PDF file with data XML and XSL. FOProcessor processor = new FOProcessor(); processor.setData("c:\\empdata.xml"); processor.setTemplate("c:\\layout1.xsl"); processor.setOutput("c:\\output.pdf"); processor.setOutputFormat(FOProcessor.FORMAT_PDF); processor.generate(); System.exit(0); } } 

留意する点は以下の2点です。
(*1) 11gでは、BI Publisher API のパッケージの階層が変更されています。
(*2) xdo-server.jarに含まれるoracle.xdo.dataengine.DataProcessorでは、setSqlが廃止されています。このため、oracle.xdo.dataengine.v1.DataProcessorを使用します。

【参考】
以下のURLが参考になります。

Oracle BI Publisher Blog
https://blogs.oracle.com/xmlpublisher/entry/setting_sql

---

[Summary]
Oracle BI Publisher provides Java API.  With these API, You can integrate your application and the document processing engine of BI Publisher.
Manual: Oracle Fusion Middleware Developer's Guide for Oracle Business Intelligence Publisher (Oracle Fusion Applications Edition)
http://download.oracle.com/docs/cd/E15586_01/fusionapps.1111/e20838/javaapis.htm

Unfortunately, the 11g API is not 100% compatible with 10g's.  And unfortunately again, as far as I've checked, no info around these changes is provided from Oracle.
But, fortunately, I found the solution somehow.


[Preparation]
Set class path for the library files listed in chapter 7.2 Prerequisites.  Note that the file name is not xdocore.jar, but it's xdo-core.jar.
Besides, you need xdo-server.jar.  This file includes DataProcessor class that generates data XML files.


[Source code]
Please see List 1.  There are two points you should note.
(*1) The package structure of BI Publisher API has been changed in 11g.
(*2) setSql method is no longer exists on oracle.xdo.dataengine.DataProcessor (xdo-server.jar).  you need to use oracle.xdo.dataengine.v1.DataProcessor instead.


[Reference]
Oracle BI Publisher Blog (English)
https://blogs.oracle.com/xmlpublisher/entry/setting_sql

エクセル帳票出力の並列実行 その2 (Excel file parallel processing, Part 2)

前回の並列実行の検証では、物理コア数までは並列度にしたがって出力性能が向上するものの、伸び率については、並列度が4倍（1→4）に増えても処理性能は2.3倍（秒間4.5ファイル→10.3ファイル）程度の伸び率にとどまることが確認されました。
今回は、この事象をOS統計から確認します。

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

Figure 3: CPU usage

1スレッドでの実行においても、コアをまたがって処理負荷が分散されています。常に5つのコアがそれぞれ60%程度の使用率で推移しており、全体での使用率は約40%（300% ÷ 800%）です。従って、CPU容量に対する伸びしろはこの時点で最大2.5倍しか残されていないと言えます。

2スレッドでは、すべてのコアに処理負荷がかかっています。各コアの使用率はそれぞれ約60%、全体としての使用率も同じく60%です。
4スレッドで各コアの使用率が90%を超えます。また、5スレッド以降のCPU使用率の動きは、他のボトルネックによってCPUの動きが妨げられている可能性を示しています。

実行モードの内訳を確認すると、Priviledged Time (sys)が2割近くを占めています。

Figure 4: CPU mode

コンテキスト・スイッチ(Context Switches)の状態は以下の通りです。以前の検証でも高い値を記録していましたが、今回も同様です。

Figure 5: Context Switches

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

Figure 6: Processor Queue Length

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

Figure 7: Memory

【ディスク】
4スレッドまでは問題ありません。5スレッド以降はDisk Timeの値が増加し始めます。

Figure 8: Disk Time (1 - Idle Time)

同様に、5スレッド以降、キューの滞留が徐々に増えています。

Figure 9: Disk Queue Length

【結論】
エクセルによる帳票出力(COMによる操作)はCPUの処理容量に依存しやすいと言えます。
また、エクセルのアプリケーション自体が10以上のプロセスを保持し、マルチスレッド処理を行っているため、エクセルのアプリケーションそのものを複数、並列に処理する場合でも処理性能が並列度に対してリニアに向上しないという点に留意する必要があります。

---

[Summary]
In the last post, multi thread processing had got poor result.  The maximum performance is 10.3 files per sec (130% increase) with four threads.
OS statistics explains the detail of this problem.


[CPU]
Figure 3 shows the CPU usage, from 1 to 8 threads.  The single thread test case(left side) uses 5 cores.  Overall CPU usage is about 40%, so the remained CPU resource is 60%.  This means that maximum performance increase ratio is 150% more ( 60 / 40 = 1.5).


Same as the past test (the original sample program), Priviledged Time (sys) gets higher than the ordinary level (Figure 4).
Context Switches seems bad in Figure 5.  Its trend is mostly same as CPU usage.
Processor Queue Length gets high from 5 to 8 threads (Figure 6).  This means too much threads causes collision and it leads lower performance.


[Memory]
Figure 7.  There seems no problem.


[Disk]
Figure 8 and 9.  Through 1 to 4 threads, no problem.  From 5 threads, Disk Time and Disk Queue Length get higher.


[Conclusion]
Excel file processing (Excel COM operation) is CPU bound.
Excel application itself manages more than 10 processes within , and perform multi thread processing.  This architecture might increase the single running performance though, there's few room to increase with running Excel application in parallel.

エクセル帳票出力の並列実行 その1 (Excel file parallel processing, Part 1)

これまでの検証で、1ファイルあたりの処理時間は0.22秒まで短縮されました。処理性能は1秒当たり4.5ファイルとなります。
今回は並列処理で単位時間当たりの処理能力の向上を図り、どの程度リニアに性能が向上するかを検証します。

【検証の方法について】
通常の運用であれば、スケジューラのジョブを並列実行させますが、今回のサンプルではプログラム内で複数スレッドを生成することで検証を行います。
検証環境は「エクセルファイルのバッチ出力 その5」と同様です。なお、今回はデバッグモードではなく、アプリケーション(.exe)を直接実行します。

【サンプルソース（GUI部）】
サンプルのソースの内、呼び出し側（GUI側）を以下の様に修正します。並列度はスライダーで指定し、並列度を１から8（論理コア数）まで変化させます。
並列実行はSystems.Threads.Tasksに含まれるParallel.Forを使用します。スレッド毎に個別のオブジェクト（エクセルおよびDBセッションを保持する）を起動し、スレッド間の競合回避を図ります。

Figure 1: Form

List 1:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Util;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;

 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int num_threads = trackBar1.Value;
            List<Util.Ora2Excel> lst = new List<Util.Ora2Excel>();

            // initialize
            for (int i = 0; i < num_threads; i++)
            {
                lst.Add(new Util.Ora2Excel());
                lst[i].OpenDb(txtUser.Text, txtPassword.Text, txtConnString.Text);
                lst[i].InitExcel();
            }

            DateTime startTime = DateTime.Now;

            Parallel.For(0, num_threads, threadID =>
            {
                for (int idx = 0; idx < 100; idx++)
                {
                    lst[threadID].OpenBook(@"c:\template_emp.xlsx", true);
                    lst[threadID].Extract(txtSql.Text, txtSheet.Text);
                    lst[threadID].SaveBook(@"c:\" + threadID.ToString() + @"\result_emp" + "_" + threadID.ToString() + "_" + idx.ToString() + ".xlsx");
                    lst[threadID].CloseBook();
                }
            });
            
            // write the result
            System.IO.StreamWriter sw = new System.IO.StreamWriter(@"c:\testResult.log", true);
            sw.WriteLine("threads: " + num_threads.ToString() + ", "  + (DateTime.Now - startTime).ToString());
            sw.Close();
            
            // close objects
            for (int j = 0; j < num_threads; j++)
            {
                lst[j].QuitExcel();
                lst[j].CloseDb();
            }

        }

        private void trackBar1_Scroll(object sender, EventArgs e)
        {
            lblThreads.Text = "threads: " + trackBar1.Value.ToString();
        }
    }
}

【サンプルソース（DLL部）】
DLL部のソースに変更はありません。ソースは「エクセルファイルのバッチ出力 その3」を参照してください。

結果は以下の通りです。4多重まで増加させた場合でも、シングルスレッド実行の場合の約2倍の性能向上にとどまりました。秒間10.3ファイルの出力性能です。性能の向上率は高いとは言えません。
また、4コア以降、多重度を上げるにつれて性能が劣化しています。

Figure 2: Performance result

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

---

[Summary]
In this post, we'll see how much we can increase Excel file processing performance by parallel (multi thread) processing.


[Environment]
Please refer to Excel file processing, Part 5.


[Sample source (GUI)]
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 Figure 1 and List 1.  The slider at the bottom of the form specifies the number of threads.  Each thread owns individual database session and Excel object.


[Sample source (DLL)]
No changes have been made.  Please refer to Excel file processing, Part 3 for the source code.


[Result]
As shown in Figure 2, the result is not so good.  The output performance hit the peak with four threads.  The peak performance is 10.3 files per sec. (100% increase from single thread performance)


In the next post, we read the OS statistics.

エクセルファイルのバッチ出力 その6 (Excel file processing, Part 6)

ここで一度、サンプルプログラム実行時のマシン負荷を確認します。

今回はプログラムを素で実行し（デバッグモードではなく、EXEを直接起動）、OS統計を収集しました。

統計の取得間隔は2秒です。

【CPU】

論理8コア（物理4コア）の内、コアを4つ使用しています。各コアの使用率は50%程度で推移しており、全体としてのCPU使用率は約25%です。

Figure 11: CPU usage during the Excel file processing

CPU使用率の内訳を確認します。全体でのPrivileged TimeとUser Time（ｓｙｓ、user）の使用率は以下の通りです。Privileged Timeが若干ですが目立ちます。

Figure 12: CPU mode

念のためコンテキストスイッチ（Context Switches）を確認します。高い値で推移しています。Excel 2010では過去のバージョンよりも多くのスレッドが使用されているため、今回のような使用パターンでは値が高止まりしやすいと推測されます。

Figure 13: Context switches

また、エクセルのプロセスを抽出したCPUの使用率の推移は以下の通りです。約200%で推移しており、前述のCPU使用率（全体で25%）と概ね一致しています。

Figure 14: CPU usage of Excel process

今回はDBサーバが同一機材上のVMに構築されているため、VMのCPU使用状況も確認します。最大でも(800%中)約6%と、ほぼ凪であり、影響は概ね無視できると考えられます。

Figure 15: CPU usage of the VM processes

【メモリ】

メモリは余裕のある状態です。グラフは割愛しますが、ページングも発生しておらず、問題は認められません。

Figure 16: Memory usage

【ディスク】

ディスクも余裕のある状態です。問題は認められません。

Figure 17: Disk usage

今回の検証では、エクセル操作はCPUへの負荷が非常に高いことが確認されました。これらの値を踏まえた上で、次回はサンプルアプリにて並列処理を検証します。

---

[Summary]

Figures above show the OS statistics during the sample program processing.

[CPU]

Figure 11 shows that the sample program (and Excel process) uses 4 cores (Intel Core i7: 4 cores w/ HT).  Each cores are used around 50%.

Figure 12 shows Privileged Time and User Time in total.  the Privileged Time is higher than ordinary level.

Figure 13 shows Context Switches get high level.  Since Excel 2010 handles more threads than the past version (Excel 2003 and older), the overhead of context switch becomes bigger issue.

Figure 14 and 15 show CPU usage of Excel process and DB server on the virtual machine.  The CPU usage of VM is low enough to be ignored.

[Memory]

no problem with the memory (Figure 16).

[Disk]

Disk is not busy at all (Figure 17).

The result shows that Excel operation costs CPU resource.  I will test parallel processing in the next post.