none
VBAよりも計算に時間がかかる RRS feed

  • 質問

  • はじめまして。よろしくお願いします。

     

    ExcelのVBAで作ったプログラムのうち、計算処理の部分だけをVB.NETに移行し、計算時間はどちらが早いのかということを試しています。この計算処理の部分はFor NextやDo Loopによる繰り返し計算が何度も行われます。VBAで10秒程度だったのが、VB.NETでは70秒程度になりました。

     

    変数の型は全て指定し、IntegerとDoubleだけを用い、「整数オーバーフローのチェックを解除」にチェックを入れましたが、他にも処理を早くするための工夫はありますでしょうか?また、VB.NETで計算するとどうしても計算時間は多くかかってしまうものなのでしょうか?

     

    コードは270行もあり貼り付けるのをためらいました。必要でしたらご連絡下さい。

    2007年10月3日 5:59

回答

  • 外池です。

     

    私が提起した項目は、いずれも空振りだったようで(汗)。ただ、ZPRB単体のテストはやった価値がありましたね。それで、Excelの関数を使っておられるとのことで、そこが原因かと。Excel関数の呼び出しは大きな時間ロスになりますよ。

     

    で、問題の関数は、NormSDist・・・、

    http://support.microsoft.com/kb/827369/ja

    の説明によれば、正規分布関数をー∞からZまで積分したもののようですね。Numerical Recipesのという本では「誤差関数」として紹介されているものに相当しますが、数値計算の実態としては、「不完全ガンマ関数」を用いることになりそうです。

     

    となりますと、私のホームページのサンプルプログラムが、かなり、参考にして頂けるのではないかと。

     

    注意点として、やはり、計算精度の問題があります。特にガンマ関数の対数の計算が必要になるのですが、Double型の精度を引き出すには、結構工夫が必要なようで、Numerical Recipesに紹介されている方法は、高々Single型程度の精度しか出ません。一方、京都大学の大浦氏の方法だとDouble型の精度が出ます。

     

     

     

    2007年10月3日 21:38

すべての返信

  • どうも

     

    可能であれば Loop 部分と主要な計算部分でもコードをのせて頂ければ内容をイメージしやすくなるのですが・・・

     

    ご検討願います。

    2007年10月3日 6:17
  • ライトさんこんにちは

    簡単な計算でテストしましたが、Excel2003 VBA よりも、VB2005の方が計算が速かったです。

     

    VB2005 -- 1秒

    コード ブロック

      Public Sub TestCalc()
        Debug.Print(DateTime.Now.ToString)
        For i As Integer = 0 To 100000000
          Dim x As Double = i * x
        Next
        Debug.Print(DateTime.Now.ToString)
      End Sub

     

     

    Excel VBA -- 7秒

    コード ブロック

    Public Sub TestCalc()
        Dim i As Long
        Dim x As Double
        Debug.Print Now
        For i = 0 To 100000000
          x = i * x
        Next
        Debug.Print Now
    End Sub

     

     

    どこで時間が掛かっているか分析した方がよいのではないでしょうか。

     

     

     

    2007年10月3日 6:27
  • 全部載せてしまいます。計算内容は積分計算です。

     

    途中のコメントで

    'Outer Integral from 0 to mode

    'Outer Integral from mode to infity
    から長い繰り返し処理が始まります。

     

     

       Public Function PTuDuVB(ByVal dbTVal As Double, _
          ByVal lngDfe As Integer, ByVal lngLvl As Integer, _
          ByVal REI() As Integer, ByVal m As Integer, _
          ByVal ML As Integer, ByVal Hypo As Integer) As Double

          Dim i As Integer
          Dim j As Integer
          Dim JS As Integer
          Dim JE As Integer
          Dim START As Double
          Dim s As Double
          Dim r As Double
          Dim q As Double

          intHypo = Hypo
          intMaxL = ML
          ReDim BT(intMaxL, intMaxL)
          ReDim TP(intMaxL, intMaxL)

          If dbTVal = 0 Then
             PTuDuVB = 1
             Exit Function
          ElseIf dbTVal > 0 Then
             blnPlus = True
          ElseIf dbTVal < 0 Then
             blnPlus = False
          End If
          q = Math.Abs(dbTVal)
          DF = lngDfe
          intIdx = lngLvl
          METHOD = m


          If METHOD = 1 Then
             JS = 1
             JE = intIdx
          Else
             JS = 1
             JE = 1
          End If

          For j = JS To JE
             For i = 1 To intIdx
                r = REI(i) / (REI(i) + REI(j))
                BT(i, j) = Math.Sqrt(1 - r)
                TP(i, j) = Math.Sqrt(r)
             Next i
          Next j

          If DF > 2000 Then
             PTuDuVB = 1 - TukDun(q)
             Exit Function
          End If

          PTuDuVB = 1

          'Outer Integral from 0 to mode
          START = Math.Sqrt((DF - 1) / DF)
          If DF <= 12 Then
             s = -0.03125
          Else
             s = -0.14 / Math.Sqrt(DF)
          End If
          PTuDuVB = PTuDuVB - AREO(q, START, s, 1)

          'Outer Integral from mode to infity
          s = 0.14 / Math.Sqrt(DF)
          PTuDuVB = PTuDuVB - AREO(q, START, s, 1.05)

          If PTuDuVB < 0 Then PTuDuVB = 0
          If PTuDuVB > 1 Then PTuDuVB = 1

          appEx.Quit()

       End Function

       Private Function AREO(ByVal q As Double, _
          ByVal START As Double, ByVal s As Double, _
          ByVal STIME As Double) As Double

          Dim SS As Double
          Dim X0 As Double
          Dim F0 As Double
          Dim X1 As Double
          Dim F1 As Double
          Dim X2 As Double
          Dim F2 As Double
          Dim dbSub As Double


          SS = s
          AREO = 0

          X0 = START
          F0 = TukDun(q * X0) * SD(X0)

          Do
             X1 = X0 + SS
             X2 = X1 + SS
             If X1 > 0 And X2 > 0 Then
                F1 = TukDun(q * X1) * SD(X1)
                F2 = TukDun(q * X2) * SD(X2)
                dbSub = Math.Abs(SS) * (F0 + 4 * F1 + F2) / 3
                AREO = AREO + dbSub
                If dbSub / AREO > cnsA Then
                   X0 = X2
                   F0 = F2
                   SS = SS * STIME
                End If
             End If

          Loop Until dbSub / AREO <= cnsA Or X2 <= 0

       End Function

       Private Function TukDun(ByVal DS As Double) As Double

          TukDun = 0

          If METHOD = 1 Then
             TukDun = AREI(DS, 0.14, 1, intIdx, 0) _
                      + AREI(DS, -0.14, 1, intIdx, 0)
          ElseIf METHOD = 2 Then
             TukDun = 2 * AREI(DS, 0.14, 1, 1, 1)
          Else
             TukDun = AREI(DS, 0.14, 1, 1, 1) _
                      + AREI(DS, -0.14, 1, 1, 1)
          End If

       End Function

       Private Function AREI(ByVal DS As Double, _
          ByVal s As Double, ByVal JS As Integer, _
          ByVal JE As Integer, ByVal DN As Integer) As Double

          Dim i As Integer
          Dim j As Integer
          Dim SS As Double
          Dim SP As Double
          Dim X0 As Double
          Dim X1 As Double
          Dim X2 As Double
          Dim F0 As Double
          Dim F1 As Double
          Dim F2 As Double
          Dim H0 As Double
          Dim H1 As Double
          Dim H2 As Double
          Dim G1 As Double
          Dim G2 As Double
          Dim dbSub As Double

          SS = s
          SP = 1 / Math.Sqrt(2 * Math.PI)
          AREI = 0
          X0 = 0
          F0 = 0

          For j = JS To JE
             H0 = SP
             For i = 1 To intIdx
                If i <> j Then
                   If METHOD = 3 Then
                      H0 = H0 * ZPRB((TP(i, j) * X0 + DS * DN) / BT(i, j))
                   Else
                      H0 = H0 * (ZPRB((TP(i, j) * X0 + DS * DN) / BT(i, j)) _
                         - ZPRB((TP(i, j) * X0 - DS) / BT(i, j)))
                   End If
                End If
             Next i
             F0 = H0 + F0
          Next j

          Do
             X1 = X0 + SS
             X2 = X1 + SS
             G1 = SP * Math.Exp(-X1 * X1 / 2)
             G2 = SP * Math.Exp(-X2 * X2 / 2)
             F1 = 0
             F2 = 0

             For j = JS To JE
                H1 = G1
                H2 = G2
                For i = 1 To intIdx
                   If i <> j Then
                      If METHOD = 3 Then
                         H1 = H1 * ZPRB((TP(i, j) * X1 + DS * DN) / BT(i, j))
                         H2 = H2 * ZPRB((TP(i, j) * X2 + DS * DN) / BT(i, j))
                      Else
                         H1 = H1 * (ZPRB((TP(i, j) * X1 + DS * DN) / BT(i, j)) _
                            - ZPRB((TP(i, j) * X1 - DS) / BT(i, j)))
                         H2 = H2 * (ZPRB((TP(i, j) * X2 + DS * DN) / BT(i, j)) _
                            - ZPRB((TP(i, j) * X2 - DS) / BT(i, j)))
                      End If
                   End If
                Next i
                F1 = H1 + F1
                F2 = H2 + F2
             Next j

             dbSub = Math.Abs(SS) / 3 * (F0 + 4 * F1 + F2)
             AREI = AREI + dbSub

             If dbSub / AREI > cnsA Then
                X0 = X2
                F0 = F2
                'ここで1.05を大きくすると収束は早いが精度が落ちる
                SS = SS * 1.05
             End If

          Loop Until dbSub / AREI <= cnsA

       End Function

       Private Function SD(ByVal X As Double) As Double
          sd = Math.Exp(DF / 2 * Math.Log(DF) + (DF - 1) * _
                Math.Log(X) - (DF / 2 - 1) * Math.Log(2) - _
                appEx.GammaLn(DF / 2) - DF / 2 * X * X)
       End Function

       Private Function ZPRB(ByVal Z As Double) As Double
          ZPRB = appEx.NormSDist(Z)
          If Z > 7.87 Then
             ZPRB = 1 - 0.0000000000000001
          ElseIf Z < -7.87 Then
             ZPRB = 0.0000000000000001
          End If
          If (intHypo = 1 And blnPlus = True) Or _
             (intHypo = 2 And blnPlus = False) Then
             ZPRB = 1 - ZPRB
          End If
       End Function

    2007年10月3日 6:30
  • ビルド後でしょうか。

    2007年10月3日 6:32
  • ・・・難しい計算式ですね

     

    計算については良くわかりませんが

    IntegerやらDouble型が符号付64ビットの精度の為に、VBAより計算に時間がかかるのでは?

     

    すみませんがご確認願います。

    2007年10月3日 6:53
  • 三輪の牛さん、返信ありがとうございます。

    たしかに単純な計算ではVB.NETの方が明らかに早いですね。

    問題となっているこのプログラムを1行ずつたどりましたが、特別ここで時間がかかっているという行は見当たりませんでした。

    変数の宣言が多くなるとメモリを食ってしまって時間がかかるのでしょうか・・・?

     

    Michael.Kさん、返信ありがとうございます。

    ビルド後に実行しています。

    ビルド前後でも結構計算時間に差が出るものですか?

     

    marumusisan返信ありがとうございます。

    IntegerをShortに、DoubleをSingleにしてみましたが、

    それでも50秒程度かかりました。

    2007年10月3日 7:23
  •  エラーが出たので試していませんが以下推測です。

     VB.NETとExcel VBAの演算精度の違いによって、ZPRBを呼び出している回数が違うのではないでしょうか。誤差収束判定しているとそのようなことは起こりえると思います。一つ一つの演算はVB.NETの方が早いはずですから。

     回数をカウントしてみるとか、誤差収束の様子を比較するとかすれば違いが出るのではないでしょうか。

    2007年10月3日 7:54
  • 外池です。

     

    最初に質問ですが、所要時間は別にして、VBAも.NETも同じ答えを返しますか? それとも、異なってる? 異なっているならどれぐらい?(打ち切り誤差と比較して、許容できそうな差ですか?)

     

    cnsAの値って、どこで定義されているのか・・・、ちょっと分からなかったのですが、システム依存性が大きく出た場合には、打ち切り誤差に少し余裕を持たせて試してみるべきです。ビット数にして2,3ビット、ですので、数倍から10倍、緩めてやって、その上で、VBAと.NETと比較してみてください。本来なら、ほぼ一致すべきだと思います。

     

    ビルド方法に関しては、Debugか、Releaseか、によって、かなり差が出ますが、DebugであってもVBAに勝るとも劣らずのパフォーマンスが出るハズだと思います。

     

    で、あと、ご指摘が出ている変数の型の問題は、IntegerやDoubleを使っている限り、一番パフォーマンスが良いはずです。基本的にすべてFPU任せになっているはずなので(組み込みのMathの関数も含めて)。

     

    私もZPRBがどのような動作をしているのかが気になります。と言うか、ZPRBが返す値なんですが・・・、ちゃんと意図したとおりの値になっているか、再度確認されたほうがよくないでしょうか? リテラルで1と非常に小さな数字の引き算を書いておられますが・・・、このような場合、実行時ではなく、コンパイル時に値が評価されるのですが、もしかすると変な値になっているかもしれませんし、VBAと.NETの差があってもおかしくない、という意味で、睨んでみるべきところだと思います。ZPRBだけ、単独で取り出して、いろいろテストしてみるべきと思います。ただ、Zが7.87の限界からはみ出るケースってどれぐらいあるんだろう・・・。そのケースだけで時間を食うことって有り得ます?

     

    2007年10月3日 8:15
  •  ライト さんからの引用

    ビルド後に実行しています。

    ビルド前後でも結構計算時間に差が出るものですか?

    VB2005の場合、経験的に、デバッグ開始(F5)と、ビルドされた、EXEの実行では、とてもスピードが違います。

    特に、Loopが、入っているとこの傾向は強いようです。

     

     

    効果があるか分かりませんが、elseif より select case の方が、速いです。

    リテラル数値は、事前に変数に入れておいた方が、速いです。

    VBAは、よく知りませんが、VB6 は、ByRef がデフォルトですが、VB2005は、ByVal がデフォルトですので、

    可能な範囲で、ByRef を指定した方が、速いです。

    function の戻しに Return を使用してみては。

    or => orelse

    and => andalso

    など思いつきました。

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    2007年10月3日 8:29
  • 三輪の牛さん、ご返信ありがとうございました。

    ループ処理でのZPRBの呼び出し回数ですが、VBAとVB.NETで同じ回数でした。

     

    外池さん、ご返信ありがとうございます。

    ●VBAとVB.NETでは超微細な差しかありません。許容できる差です。

    VBA       0.182234034604679

    VB.NET  0.182234034604669

    ●cnsAはひとつ上のクラス内で宣言しています。この値がループ処理の基準になりますから、確かにこの値を多少緩めるのはひとつの手ですね。

    ●ビルド方法のDebugかReleaseかというのはメニューでいうとそれぞれがどれに相当するのですか?

    ●ZPRBの返す値はVBAとVB.NETで同じでした。が、気になってZPRBを5万回呼び出してみたら、VBAでは1秒、VB.NETでは43秒でした。原因はExcelの関数を計算に使っていることだと思われます。横着しないでExcel関数と同じ結果を出すものを自分で書くべき、のようです。

     

    Michael.Kさん、ご返信ありがとうございます。

    細かい部分まで見ていただいてありがとうございます。

    是非活用します!

    2007年10月3日 9:12
  • 外池です。

     

    私が提起した項目は、いずれも空振りだったようで(汗)。ただ、ZPRB単体のテストはやった価値がありましたね。それで、Excelの関数を使っておられるとのことで、そこが原因かと。Excel関数の呼び出しは大きな時間ロスになりますよ。

     

    で、問題の関数は、NormSDist・・・、

    http://support.microsoft.com/kb/827369/ja

    の説明によれば、正規分布関数をー∞からZまで積分したもののようですね。Numerical Recipesのという本では「誤差関数」として紹介されているものに相当しますが、数値計算の実態としては、「不完全ガンマ関数」を用いることになりそうです。

     

    となりますと、私のホームページのサンプルプログラムが、かなり、参考にして頂けるのではないかと。

     

    注意点として、やはり、計算精度の問題があります。特にガンマ関数の対数の計算が必要になるのですが、Double型の精度を引き出すには、結構工夫が必要なようで、Numerical Recipesに紹介されている方法は、高々Single型程度の精度しか出ません。一方、京都大学の大浦氏の方法だとDouble型の精度が出ます。

     

     

     

    2007年10月3日 21:38
  • 外池さん、ご返信ありがとうございました。

     

    やはりExcel関数を呼ぶと時間がかかってしまうんですね。

    今回のプログラムでは「NormSDist」と「GammaLn」の2つのExcel関数を利用しています。ですから、これらを計算できるソースを昨夜探しまして、無事に完成しました。時間はほぼ0秒という結果です。

     

    ガンマ関数については、今気付いたのですが、偶然にも外池さんのHPを活用させていただきました。必然的に辿り着いたのかもしれませんね。「Numerical Recipe」は手元にあるのですが、本を開く前にウェブで検索してましたw

    http://homepage3.nifty.com/numericworld/computer/vb/gamma.htm

     

    正規分布曲線に関するソースについては、「科学技術計算」様を利用しました。

    http://www5.airnet.ne.jp/tomy/cpro/sst10.htm

     

    なお、これらを利用した計算結果の精度も申し分ありませんでした。

    この度は本当にありがとうございました。
    2007年10月4日 2:59