 |
| Figure 3: CPU usage |
 |
| Figure 4: Processor Queue Length |
 |
| Figure 5: Context Switches / sec |
 |
| Figure 6: Available Memory (MB) |
 |
| Figure 7: Disk usage |
 |
| Figure 8: GC |
 |
| Figure 3: CPU usage |
 |
| Figure 4: CPU mode |
 |
| Figure 5: Context Switches |
 |
| Figure 6: Processor Queue Length |
 |
| Figure 7: Memory |
 |
| Figure 8: Disk Time (1 - Idle Time) |
 |
| Figure 9: Disk Queue Length |
 |
| Figure 1: Form |
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();
}
}
} |
| Figure 2: Performance result |
 |
| Figure 11: CPU usage during the Excel file processing |
 |
| Figure 12: CPU mode |
 |
| Figure 13: Context switches |
 |
| Figure 14: CPU usage of Excel process |
 |
| Figure 15: CPU usage of the VM processes |
 |
| Figure 16: Memory usage |
 |
| Figure 17: Disk usage |
 |
| Figure 10: Machine environment |
 |
| Figure 9: Light template file is processed faster. |
public void Extract(string sql, string SheetName)
{
const int XL_WHOLE = 1;
const int XL_DOWN = -4121;
const string START_TAG = "$start";
int colCount;
List<object[]> lst = new List<object[]>();
DateTime lapTime;
lapTime = DateTime.Now;
OleDbDataReader rst = GetRecordset(sql); // get recordset
colCount = rst.FieldCount;
Debug.WriteLine("[query] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
while (rst.Read()) // output loop
{
object[] ColValues = new Object[rst.FieldCount];
for (int idxCol = 0; idxCol < rst.FieldCount; idxCol++)
ColValues[idxCol] = rst.GetValue(idxCol);
lst.Add(ColValues);
}
Debug.WriteLine("[set row val] " + (DateTime.Now - lapTime).ToString());
rst.Close();
// convert list => array
lapTime = DateTime.Now;
object[,] Values2Copy = new Object[lst.Count, colCount];
for (int idxRow = 0; idxRow < lst.Count; idxRow++)
for (int idxCol = 0; idxCol < colCount; idxCol++)
Values2Copy[idxRow, idxCol] = lst[idxRow][idxCol];
Debug.WriteLine("[list to array] " + (DateTime.Now - lapTime).ToString());
// copy array into excel sheet.
lapTime = DateTime.Now;
xSheet = xBook.Worksheets[SheetName]; // activate start position
Debug.WriteLine("[activate sheet] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
xSheet.Cells.Find(What: START_TAG, LookAt: XL_WHOLE).Activate();
Debug.WriteLine("[find $start] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
int idxStart = xApplication.ActiveCell.Row;
xSheet.Rows[xApplication.ActiveCell.Row].Copy();
xSheet.Rows[(idxStart + 1).ToString() + ":" + (idxStart + lst.Count-1).ToString()].Insert(Shift: XL_DOWN);
Debug.WriteLine("[insert row] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
Range r = xApplication.ActiveCell;
r = r.get_Resize(lst.Count, colCount);
r.set_Value(Missing.Value, Values2Copy);
Debug.WriteLine("[copy into range] " + (DateTime.Now - lapTime).ToString());
} |
| Figure 7: Events |
 |
| Figure 8: Events in DLL |
private void button1_Click(object sender, EventArgs e)
{
DefaultTraceListener dtl = (DefaultTraceListener)Debug.Listeners["Default"];
dtl.LogFileName = @"c:\Sample.log";
Util.Ora2Excel x = new Util.Ora2Excel();
x.OpenDb(txtUser.Text, txtPassword.Text, txtConnString.Text);
x.InitExcel();
DateTime lapTime;
DateTime startTime = DateTime.Now;
for (int i = 0; i < 100; i++)
{
lapTime = DateTime.Now;
x.OpenBook(txtTemplate.Text);
Debug.WriteLine("[file open] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
x.Extract(txtSql.Text, txtSheet.Text);
Debug.WriteLine("[extract] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
x.SaveBook(@"d:\result_emp" + i.ToString() + ".xlsx");
Debug.WriteLine("[save] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
x.CloseBook();
Debug.WriteLine("[close] " + (DateTime.Now - lapTime).ToString());
}
Debug.WriteLine("[total] " + (DateTime.Now - startTime).ToString());
x.QuitExcel();
x.CloseDb();
} |
| Figure 5: Events |
public void Extract(string sql, string SheetName)
{
const int XL_WHOLE = 1;
const int XL_DOWN = -4121;
const string START_TAG = "$start";
int idxRow;
DateTime lapTime;
lapTime = DateTime.Now;
OleDbDataReader rst = GetRecordset(sql); // get recordset
Debug.WriteLine("[query] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
xSheet = xBook.Worksheets[SheetName]; // activate start position
Debug.WriteLine("[activate sheet] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
xSheet.Cells.Find(What: START_TAG, LookAt: XL_WHOLE).Activate();
Debug.WriteLine("[find $start] " + (DateTime.Now - lapTime).ToString());
idxRow = xApplication.ActiveCell.Row;
while (rst.Read()) // output loop
{
lapTime = DateTime.Now;
xSheet.Rows[idxRow].Copy();
xSheet.Rows[idxRow+1].Insert(Shift: XL_DOWN);
Debug.WriteLine("[insert row] " + (DateTime.Now - lapTime).ToString());
lapTime = DateTime.Now;
for (int idxCol = 0; idxCol < rst.FieldCount; idxCol++)
xSheet.Cells[idxRow,idxCol+1].value = rst.GetValue(idxCol);
Debug.WriteLine("[set cell val] " + (DateTime.Now - lapTime).ToString());
idxRow++;
}
rst.Close();
} |
| Figure 6: Events in DLL |
 |
| Figure 1: Runtime property setting |
所要時間（秒） | 秒間出力ページ数 | |
1回目 | 199.7 | 50.0 |
2回目 | 180.5 | 55.4 |
3回目 | 201.9 | 49.5 |
平均 | 194.0 | 51.7 |
 |
| Figure 2: CPU usage |
 |
| Figure 3: Memory |
 |
| Figure 4: Disk time |
 |
| Figure 5: Garbage Collection status (default setting) |
所要時間（秒） | 秒間出力ページ数 | |
1回目 | 82.3 | 121.5 |
2回目 | 82.3 | 121.5 |
3回目 | 84.0 | 118.9 |
平均 | 82.9 | 120.7 |
 |
| Figure 6: Garbage Collection (Heap=1GB) |
 |
| Figure 7: CPU usage |
 |
| Figure 8: Memory |
 |
| Figure 9: Disk Time |
Elapsed Time(sec) | pages / sec | |
1st | 199.7 | 50.0 |
2nd | 180.5 | 55.4 |
3rd | 201.9 | 49.5 |
Average | 194.0 | 51.7 |
Elapsed Time(sec) | pages / sec | |
1st | 82.3 | 121.5 |
2nd | 82.3 | 121.5 |
3rd | 84.0 | 118.9 |
Average | 82.9 | 120.7 |