VBAでCollectionのループ方法をFor Eachに書き換えるだけで100倍高速になった

今、ExcelのVBAでちょっとしたものを作ってるのですが、コーディング中に急にパフォーマンスが悪くなるという現象が発生したので、原因を調べてみたらCollectionの各要素を処理する場面が問題のようだった。CollectionをFor Eachで周すようにしただけで、さっきの遅さが嘘のように解決してしまった。

本当にFor i = … のループを For Eachに書き換えただけで速くなるのか確信持てなかったし、他の原因も重なった結果かも、という疑惑も拭いきれない状態だったので、ExcelXPでベンチマークを取ってみました。

主要部分

下のLoop_for_indexとLoop_for_eachを比較しました。

'For i = ...版。 各要素を添字指定でアクセスする
Public Sub Loop_for_index()
  Dim dummy As Long
  Dim i As Long
  For i = 1 To list.Count
    Dim c As EmptyClass
    Set c = list(i)
    dummy = dummy + c.dummy
  Next
End Sub
 
'For Each版。 For Eachで直接アクセスする
Public Sub Loop_for_each()
  Dim dummy As Long
  Dim c As EmptyClass
  For Each c In list
    dummy = dummy + c.dummy
  Next
End Sub

結果

計測の結果、For Each版は、For i = …版より、100倍高速でした。

For Each版の方が速そうだと思っていたけど、100倍はいくらなんでも差がありすぎなんじゃないか？

For i = …版の書き方に問題があるんでしょうか？

もうちょっと調べてみると、単純に100倍というのは大袈裟で、Collectionの要素数が少ない(1000以下程度)場合は、数倍の差しかありませんでした。

とすると、問題はCollectionの要素が多い場合のFor i = …版の遅さか。

VBA使いの人には、あたり前の話なのかもというのも疑って、「vba for each 高速」で調べてみると、逆のことが書いてあるエントリを見つけて更に混乱してきた。

結論

コレクションに属する個々の要素を列挙する場合には「For～Next ステートメント」の方が「For Each～Next ステートメント」よりも高速である。

■T’sWare Access Labo #18 ～For～NextとFor Each～Nextを比較する～

単純に、「VBAで大量のデータを扱うな」という話なのかもしれない。

以下が、今回計測に使用した全スクリプトです。

EmptyClass

計測で利用するクラス。

Option Explicit
 
Public dummy As Integer

Timer

ミリ秒で計測するためのモジュール

Option Explicit
 
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
 
Public startTime As Long
 
Sub StopWatchStart()
  startTime = timeGetTime()
End Sub
 
Function StopWatchStop() As Long
  StopWatchStop = timeGetTime() - startTime
End Function

Module1

ベンチマークスクリプト

Option Explicit
 
Public list As Collection
 
'前処理 データの準備
Public Sub Setup()
  Set list = New Collection
  Dim i As Long
  Dim c As EmptyClass
  For i = 1 To 10000
    Set c = New EmptyClass
    Call list.Add(c)
  Next
End Sub
 
'For i = ...版。 各要素を添字指定でアクセスする
Public Sub Loop_for_index()
  Dim dummy As Long
  Dim i As Long
  For i = 1 To list.Count
    Dim c As EmptyClass
    Set c = list(i)
    dummy = dummy + c.dummy
  Next
End Sub
 
'For Each版。 For Eachで直接アクセスする
Public Sub Loop_for_each()
  Dim dummy As Long
  Dim c As EmptyClass
  For Each c In list
    dummy = dummy + c.dummy
  Next
End Sub
 
Public Sub main()
  Dim i As Integer
  Call Setup
  'Loop_for_index の実行
  For i = 1 To 10
    Call StopWatchStart
    Call Loop_for_index
    Debug.Print "Loop_for_index " & StopWatchStop & "ms"
  Next
  'Loop_for_each の実行
  For i = 1 To 10
    Call StopWatchStart
    Call Loop_for_each
    Debug.Print "Loop_for_each " & StopWatchStop & "ms"
  Next
End Sub

実行結果

Loop_for_index 329ms
Loop_for_index 293ms
Loop_for_index 298ms
Loop_for_index 294ms
Loop_for_index 297ms
Loop_for_index 291ms
Loop_for_index 294ms
Loop_for_index 294ms
Loop_for_index 294ms
Loop_for_index 296ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 2ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 3ms
Loop_for_each 3ms

5件のコメント

ap 2011/04/10

1年も前のエントリだけど・・・コメントできないのは文字数制限でしょうか。。

試してみたら、原因が分りました。

・Collectionへのindexアクセスは、index値が大きいほど時間がかかる。
・Keyアクセスは、要素数の影響は僅か。
・For Eachは、Collectionの要素取得がやたら早い。
ap 2011/04/10

書けた！けど改行が・・・(p_q)ｼｸｼｸ

◆検証：　特定の要素に10000回ずつアクセス。以下、それぞれをループ。

Set c = list(“1”) -> 20.4ms

Set c = list(“10000”) -> 24.0ms

Set c = list(1) -> 8.7ms

Set c = list(10000) -> 601.9ms ?!Σ(ﾟ□ﾟ；)

エントリの実行結果＝index 300msは、9～600msの平均だと分ります。
indexの方が早いのはi=300まで。要素が多い場合はkeyが安定するようです。

更に、For Eachの3msは、list(1)と比べても早く、処理が効率化されているようです。
ただ、配列など元々高速なデータでは、余計な内部処理が足を引っ張るのか、For i の方が僅かに早くなります。
smeghead 2011/04/10

apさん
ありがとうございます。

indexの方が早いのはi=300まで。要素が多い場合はkeyが安定するようです。

Collectionの要素取得の方法の違いによる検証ありがとうございます。

ただ、配列など元々高速なデータでは、余計な内部処理が足を引っ張るのか、For i の方が僅かに早くなります。

ネイティブな配列か、Collectionかの違いが大きく影響してるということですか。>他の人がFor i の方が速いと言っていた件など
ネイティブな配列の場合の方が、最適化を頑張っているのかもしれません。(使われる頻度が高いという判断から)
ap 2011/04/11

リンク先の”Fields”もデータベース関係ですし、配列同様、もともと最適化されているんでしょうね。
高級言語育ちでメモリ上の構造とかには弱いので、作りが見えてくるとスッキリします。いい勉強になりました。
かずな 2012/10/29

CollectionとVariant()で結果が逆だった。
CollectionはLinked Listなのかな？

週記くらい