Likes Likes:  0
Results 1 to 4 of 4

Thread: speed performance measure vba function

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default speed performance measure vba function

    Hi all,

    if i want to measure how long a cell take to recal, do you have a vba function that can use on a cell or a range to show me the time it takes to recal in 0.0000 of a sec for that particular cell or range. basically as accurate as possible 5 to 6 decimal sec will do.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,886
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    7 Thread(s)

    Default Re: speed performance measure vba function

    According to this article: Improving Performance in Excel 2007
    at the time Excel 2007 was introduced, the highest resolution timer had a resolution on the order of microseconds. You might want to use the MicroTimer function in the referenced article.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,255
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: speed performance measure vba function

    I have been debating about whether readers would find this interesting or an annoying nitpick. Some feedback would be appreciated.

    Quote Originally Posted by JoeMo View Post
    According to this article: Improving Performance in Excel 2007[.] at the time Excel 2007 was introduced, the highest resolution timer had a resolution on the order of microseconds. You might want to use the MicroTimer function in the referenced article.
    That is incorrect; and that is not exactly what the article says.

    The resolution of the performance counter is 1/freq, where "freq" is the counts-per-second returned by QueryPerformanceFrequency. (Actually, it is 1/(freq*10000), since freq is type Currency.)

    That depends on the computer [*], not the version of Office (although that might not have been JoeMo's intent). For example, it is under 280 nanoseconds (0.280 microseconds) on one of my computers, and it is under 480 nsec on another. Surprisingly, the latter is the newer and faster computer.

    [*] By "computer", I mean the combination of hardware, BIOS and perhaps the version of Windows.

    The article says, correctly, that the implementation of MicroTimer "can measure time intervals down to small numbers of microseconds". Call that accuracy. That is due, in part, to VBA overhead in the MicroTimer implementation.

    My point about "resolution" v. "accuracy" is: assuming the timer overhead is constant (it isn't, especially for MicroTimer!), the difference between two timed intervals (e.g. two formulas) can be measured to within a fraction of a microsecond, not "small numbers of microseconds". It is limited by the resolution, not the accuracy.

    That's really a nitpick. The important take-away is: QueryPerformanceCounter is indeed the tool of choice for high-resolution time measurement.

    However, it should also be noted that a precision instrument is only as good as the person using it. There is much more to performance measurement than the precision of the data. It is as much art as it is science.

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,255
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: speed performance measure vba function

    I have been debating about whether readers would find this interesting and useful, or just annoying detail. Some feedback would be appreciated.

    Quote Originally Posted by archer18 View Post
    if i want to measure how long a cell take to recal, do you have a vba function that can use on a cell or a range to show me the time it takes to recal in 0.0000 of a sec for that particular cell or range. basically as accurate as possible 5 to 6 decimal sec will do.
    JoeMo points to the use of QueryPerformanceCounter described in the MS article "Improving Performance" by Charles Williams (click here). That article contains a wealth of information that applies to isolating bottlenecks in workbook designs.

    Many people try to apply the same methodology to measuring and comparing the performance of specific Excel functions and individual formula design.

    If that is your purpose, beware that that road is fraught with pitfalls that I feel many people fall prey to, often reporting dubious numbers and sometimes reaching incorrect conclusions. The precision of QueryPerformanceCounter can be misleading. The interpretation of performance measurements is as much art as it is science.

    Consider the following experiment (#1)....

    In a new workbook, enter =RAND() into A1, and copy A1 into A2:A10000.

    Then enter the following code and execute procedure "doit" to measure the performance of just the formula in A1. (The guts of the performance measure is highlighted in red.)
    Code:
    Option Explicit
    
    Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
        (ByRef freq As Currency) As Long
    Public Declare Function QueryPerformanceCounter Lib "kernel32" _
        (ByRef cnt As Currency) As Long
        
    Sub doit()
        Dim sc As Currency, ec As Currency, dt As Double
        Dim s As String, i As Long, n As Long
        Dim oldCalc, myRng As Range
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            oldCalc = .Calculation
            .Calculation = xlCalculationManual
        End With
        Set myRng = Range("a1")
        n = myRng.Count
        s = myRng.Address & "   " & Format(n, "#,##0")
        For i = 1 To 10
            sc = myTimer
            myRng.Calculate
            ec = myTimer
            dt = myElapsedTime(ec - sc)
            s = s & vbNewLine & _
                "    " & Format(dt / n, "0.000\,000\,000") & " sec" & _
                "    " & Format(dt, "0.000\,000\,000") & " sec"
        Next
        With Application
            .EnableEvents = True
            .Calculation = oldCalc
            .ScreenUpdating = True
        End With
        MsgBox s
    End Sub
    
    Function myTimer() As Currency
        ' defer conversion to seconds until myElapsedTime
        QueryPerformanceCounter myTimer
    End Function
    
    Function myElapsedTime(dc As Currency) As Double  ' return seconds
        Static df As Double
        Dim freq As Currency
        If df = 0 Then QueryPerformanceFrequency freq: df = freq
        myElapsedTime = dc / df
    End Function
    On one computer, I see results like the following (YMMV):
    Code:
    $A$1   1
        0.003,482,007 sec    0.003,482,007 sec
        0.000,051,124 sec    0.000,051,124 sec
        0.000,045,537 sec    0.000,045,537 sec
        0.000,044,419 sec    0.000,044,419 sec
        0.000,044,419 sec    0.000,044,419 sec
        0.000,044,419 sec    0.000,044,419 sec
        0.000,044,140 sec    0.000,044,140 sec
        0.000,044,419 sec    0.000,044,419 sec
        0.000,044,698 sec    0.000,044,698 sec
        0.000,044,978 sec    0.000,044,978 sec
    You might notice some oddities, to wit: all the measurements are somewhat different; and usually, the first measurement is significantly different. I will discuss these below.

    For now, only note that the average is about 44.6 microseconds, excluding the first two measurements ("outliers"). Should we conclude that Excel RAND takes nearly 45 microseconds?(!)

    Now, consider experiment #2....

    Make the change indicated in red below, and re-execute procedure "doit" to measure the performance of all the formulas in A1:A10000.
    Code:
        Set myRng = Range("a1", Cells(1, "a").End(xlDown))
        n = myRng.Count
        s = myRng.Address & "   " & Format(n, "#,##0")
        For i = 1 To 10
            sc = myTimer
            myRng.Calculate
            ec = myTimer
            dt = myElapsedTime(ec - sc)
            s = s & vbNewLine & _
                "    " & Format(dt / n, "0.000\,000\,000") & " sec" & _
                "    " & Format(dt, "0.000\,000\,000") & " sec"
        Next
    On my computer, I see the following results (YMMV):
    Code:
    $A$1:$A$10000   10,000
        0.000,001,289 sec    0.012,886,275 sec
        0.000,000,934 sec    0.009,343,646 sec
        0.000,000,955 sec    0.009,549,258 sec
        0.000,000,960 sec    0.009,600,941 sec
        0.000,000,996 sec    0.009,957,690 sec
        0.000,000,930 sec    0.009,296,712 sec
        0.000,000,950 sec    0.009,498,135 sec
        0.000,001,360 sec    0.013,601,170 sec
        0.000,000,945 sec    0.009,450,084 sec
        0.000,000,971 sec    0.009,712,966 sec
    The numbers on the right are the total execution time for 10,000 formulas. The numbers of the left are the average per-formula execution time, by simply dividing the right by 10,000.

    Again, let's overlook some interesting oddities for now.

    Note only that excluding "outliers" (#1 and #8), the average per-formula time is about 0.9 microseconds, 1/50th of the time we measured for a single formula in experiment #1.

    Did RAND get faster when we executed more of them? Of course not!

    In fact, 0.9 microseconds is closer to the real execution time (on my computer).

    We were fooled by the single-formula time of 45 microseconds because all of our attempts to measure performance incur overhead. In this case, significant overhead relative to the short interval we tried to measure in experiment #1.

    The following is a brief and over-simplistic list of some sources of that overhead:
    • VBA late-compilation overhead the first time we execute a code path.
    • Overhead in interprocess communication between VBA and Excel threads.
    • System overhead in process management to switch between VBA and Excel threads, which might include paging in code and data.
    • Excel overhead in initiating the calculation.
    • Excel and system overhead in managing the workbook data space.
    • System overhead in processing interrupts, some of which stop all other processing, even on multi-core computers (notably system clock interrupts).

    Each of these overhead factors is variable and unpredictable. This is a key source of execution time variations, especially the "outliers".

    And since we incur all of these overhead factors each time we execute range.Calculate in VBA, we cannot reduce their effect simply by executing range.Calculate multiple times in a loop.

    (On the other hand, the value in such a loop is to recognize and account for execute time variation.)

    The important take-away is: usually, we cannot measure the performance of "a" formula simply by measuring one instance of the formula.

    But that does depend on the nature of the formula and the situation that we are trying to measure. Sometimes we need to measure one instance of a formula, but increase the size of ranges that it references in order to overcome the effects of overhead.

    (And sometimes, the Heisenberg Uncertainty Principle applies: changes we must make to improvement the accuracy of a performance measurement also changes circumstances and introduces new inaccuracies.)

    I have only touched on the issue; this is only the tip of the iceberg. But I hope it is enough to recognize the potential effect of some performance measurement pitfalls, if not avoid them.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •