アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < 転職した

VBAのパフォーマンス調査に関する報告書

前の会社で書いた報告書を見つけたので、サクッとネットの海へ放流。

バリバリ報告書っぽい体裁になってたので、ふんわり意訳版。


概要

  • ExcelVBAのパフォーマンス調査の結果&考察。

測定準備

  • 動作環境(やばい古い)
  • 測定方法
    • if文を用いて1つのコードにつき10 回の測定を行い、平均値も考慮して結果を比較する
    • 測定を正確に行うため、以下のWindows APIを使用して時間を測定するためのクラスを用いた
      • QueryPerformanceCounter:コンピュータが起動してからのクロック数を求める
      • QueryPerformanceFrequency:クロックの周波数を求める
    • 測定に使用したプロシージャと時間測定用クラスのソースコードは後述
  • 測定内容

測定結果・考察

画面の更新(画面更新の有無による処理時間の違いを調査する)
  • 測定用コード
' // ケースA:画面更新を行う
Dim i As Integer, j As Integer
Application.ScreenUpdating = True
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 54, 17).Select
    Selection.Value = j
  Next j
Next i

' // ケースB:画面更新を行わない
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 54, 18).Select
    Selection.Value = j
  Next j
Next i
  • 測定結果・考察
    • 詳細な結果は下の表のとおり
    • 画面の更新を行わない場合は、更新を行う場合の約5分の1の時間で処理
    • 画面の更新を止めておくと処理速度の大幅な上昇が期待できる
試行 A B (B/A)*100
1 492.7216 97.69845 19.82833
2 488.2042 97.89708 20.05248
3 492.0748 95.996 19.50841
4 492.6925 96.52847 19.59203
5 489.2806 95.87084 19.59425
6 488.0673 96.76733 19.82663
7 487.2351 96.22284 19.74875
8 489.0856 96.83409 19.79901
9 491.049 96.30749 19.6126
10 487.2404 95.94348 19.6912
平均 489.7651 96.60661 19.72537
Selectメソッドの使用(セルの選択の有無による処理時間の違いを調査する)
  • 測定用コード
' // ケースC:Selectを使用する
Dim i As Integer, j As Integer
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 53, 4).Select
    Selection.Value = j
  Next j
Next i

' // ケースD:Selectを使用しない
Dim k As Integer, l As Integer
For k = 1 To 100
  For l = 1 To 10
    Cells(l + 53, 5).Value = l
  Next l
Next k
  • 測定結果・考察
    • 詳細な結果は下の表のとおり
    • Selectを使用しない場合は使用する場合に比べて17%ほどの時間で処理
    • ただしケースDは実行時間の誤差が大きく、期待通りの時間短縮にならないこともある
試行 C D (D/C)*100
1 465.02167 78.934334 16.974334
2 457.84478 80.925928 17.675407
3 459.8509 78.987972 17.176866
4 459.09326 80.210753 17.47156
5 459.11478 79.451159 17.305293
6 458.91699 78.998029 17.214013
7 460.53646 78.367223 17.016508
8 459.17652 79.591121 17.333448
9 461.22426 79.251134 17.182776
10 458.37055 79.163693 17.270676
平均 459.91502 79.388135 17.262088
Value プロパティの記述(Value プロパティを省略する場合と記述する場合の処理時間の違いを調査する)
  • 測定用コード
' // ケースE:Valueプロパティを省略する
Dim i As Long
For i = 1 To 10000
  Range("F54") = i
Next i

' // ケースF:Valueプロパティを記述する
Dim i As Long
For i = 1 To 10000
  Range("F54").Value = i
Next i
  • 測定結果・考察
    • 詳細な結果は下の表のとおり
    • 処理時間はほぼ同じで、Value プロパティの省略は実行時間に影響を与えないと考えてよい
試行 E F (F/E)*100
1 2326.4577 2345.0455 100.79898
2 2324.4613 2337.1275 100.54491
3 2329.7536 2335.5049 100.24686
4 2347.5447 2331.6855 99.324431
5 2339.4532 2349.2494 100.41874
6 2326.0666 2335.1348 100.38985
7 2323.0444 2329.5581 100.28039
8 2345.9035 2330.3953 99.338928
9 2329.1851 2362.5732 101.43346
10 2330.7468 2335.5527 100.2062
平均 2332.2617 2339.1827 100.29827
セルの指定方法(セルの指定方法による処理時間の違いを調査する)
  • 測定用コード
' // ケースG:Rangeプロパティ
Dim i As Long
For i = 1 To 10000
  Range("F56") = Rnd
Next i

' // ケースH:Cellsプロパティ
Dim i As Long
For i = 1 To 10000
  Cells(56, 6) = Rnd
Next i
  • 測定結果・考察
    • 詳細な結果は下の表のとおり
    • RangeプロパティよりもCellsプロパティを使用した場合の方が早く処理を終えている
      • ただし差は5%ほど
試行 G H (H/G)*100
1 2421.3896 2277.0321 94.038239
2 2389.8699 2291.2066 95.871601
3 2387.951 2282.2504 95.573586
4 2404.8618 2299.6845 95.626471
5 2381.5926 2285.0334 95.945604
6 2388.6293 2277.1433 95.332639
7 2388.7131 2277.4294 95.34127
8 2397.8883 2279.875 95.078448
9 2382.8615 2295.9938 96.354478
10 2383.6088 2285.588 95.887713
平均 2392.7366 2285.1236 95.505005
セルの参照に配列を使用する(配列に移したデータを参照するか、セルを直接参照するかの違いを調査する)
  • 測定用コード
' // 測定前にC81~CX10080 へ0~100 の整数の乱数を入力しておく

' // ケースI:セルを直接参照する
Dim i As Long, j As Long, buf As Long
For i = 1 To 10000
  For j = 1 To 100
    buf = Cells(i + 80, j + 2)
  Next j
Next i

' // ケースJ:セルを配列に格納してから配列を参照する
Dim i As Long, j As Long, buf As Long, C As Variant
C = Range("C81:CX10080")
For i = 1 To 10000
  For j = 1 To 100
    buf = C(i, j)
  Next j
Next i
  • 測定結果・考察
    • 詳細な結果は下の表のとおり
    • セルを配列に格納してからデータを参照した方が、約7%という短時間で処理を終えている
    • 多くのデータを扱う場合は特に、配列に格納し使用すると、処理速度の大幅な上昇が期待できる
試行 I J (J/I)*100
1 9563.1584 645.33789 6.7481668
2 9568.8748 661.04072 6.9082388
3 9557.432 659.11617 6.8963731
4 9566.9553 660.18083 6.9006367
5 9555.9391 658.64293 6.8924982
6 9569.343 659.31145 6.8898298
7 9562.0421 661.05944 6.9133709
8 9554.2299 658.96671 6.8971201
9 9553.607 663.79163 6.9480734
10 9585.2875 668.88389 6.9782351
平均 9563.6869 659.63317 6.8972543

使用した測定プログラムのソースコード

メインプロシージャ
Public Sub SpeedComp ()
  Dim cnt As Long
  Dim myQPT As New QueryPerformanceTimer '*** 測定クラスのインスタンス化
  For cnt = 1 To 10 '*** 10 回試行するため
    Call myQPT.BeginTimer '*** 測定開始
    '**********************************
    '*** 時間を計りたい処理のコード ***
    '**********************************
    Cells(cnt, 1).Value = myQPT.EndTimer '*** 測定終了、測定結果を格納
  Next
End Sub
測定用クラス
'! @class QueryPerformanceTimer
'! @brief 計測を行うためのタイマ
'! @date 2009/07/02

'* WindowsAPI の関数を使用のため
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Boolean
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Boolean

'* 変数宣言
Private m_curFreq As Currency '* 周波数
Private m_curStartTime As Currency '* 開始時刻

'*
'* メソッド:計測の可否を調べ、可能なら開始時間を取得する
'* @return 時間取得に成功ならTrue、失敗ならFalse
'*
Public Function BeginTimer() As Boolean
  '* 周波数未取得のとき
  If m_curFreq = 0 Then
    '* 高分解能パフォーマンスカウンタの周波数取得に失敗時False を返す
    If QueryPerformanceFrequency(m_curFreq) = False Then
      BeginTimer = False
    End If
  End If
  '* 開始時間を取得し、True を返す
  Call QueryPerformanceCounter(m_curStartTime)
  BeginTimer = True
End Function

'*
'* メソッド:計測開始時と終了時の差をとり、ミリ秒換算する
'* @return 計測時間(単位:ミリ秒)計測できていない場合は-1
'*
Public Function EndTimer() As Double
  '* 変数宣言
  Dim curEndTime As Currency '* 終了時刻
  '* 周波数が取得できている場合
  If m_curFreq <> 0 Then
    '* 終了時間を取得
    Call QueryPerformanceCounter(curEndTime)
    '* 終了時間と開始時間の差をクロック周波数で除算し、ミリ秒換算する
    EndTimer = ((curEndTime - m_curStartTime) / m_curFreq) * 1000
    '* 周波数が取得できていない場合は-1 を返す
  Else
    EndTimer = -1
  End If
End Function

'*
'* 起動時の処理
'*
Private Sub Class_Initialize()
  '* Private 変数の初期化
  m_curFreq = 0
  m_curStartTime = 0
End Sub

参考文献