none
Slow macro execution (Range.Copy) when running multiple instances of Excel 2010 and later RRS feed

  • General discussion

  • Hello,

    Throughout the thread of Slow Excel 2010 Macro execution (https://social.msdn.microsoft.com/Forums/office/en-US/858c1c9d-a347-473d-8c81-829e22b6f592/slow-excel-2010-macro-execution?forum=exceldev). Namely slow execution of the Range.Copy methods in macros in Office 2010 and above versions.

    As I understand it was fixed - Macro takes longer than expected to execute many individual copy and paste operations in Excel 2010 and later (https://support.microsoft.com/en-us/help/2817672/macro-takes-longer-than-expected-to-execute-many-individual-copy-and-p). It really works and helps to increase speed, but only in the case of 1 instance of Excel.

    But in cases where several instance of Excel are working in parallel, the speed is still much slower than it was in Office 2007 and in previous versions.

    I investigated the reasons for these slowdowns and found out that in Office 2010 and later, the process of clipboard cleaning has changed:

    • Office 2007 and later: only 1 attempt was made to clear the clipboard, and after that the process went further regardless of the result
    • Office 2010 and later: about 10 attempts are made to clear the clipboard, and after each unsuccessful attempt, Sleep 200ms is called.

    Thus, with competing work of several instances of Excel, each operation with the clipboard takes up to 2 seconds (10 x 200ms).
    This seriously slows down the work of all simultaneously working instances of Excel, for example, if several reports are executed in parallel.

    By the way, according to observations, the result of Range.Copy does not depend on the clipboard at all:

    • after an unsuccessful attempt to clear the clipboard: the result of the macro execution is still correct
    • if you intercept all WinApi functions that work with the clipboard and return an error instead of calling the original function: the result of the macro execution also is still correct

    For what purposes is it necessary to save the data to the clipboard for the Range.Copy method?

    We understand that using Range.Copy method become dangerous for performance (at least in versions 2010 and later). But, on the other hand, Microsoft did not provide alternatives that would not use the clipboard.

    It is verified that the described slowdown effect is preserved in all versions of Office from 2010 to 2019.

    Technical details.
    WinApi calls were traced using the trcapi32.dll library and syelogd.exe from Microsoft Detours (https://github.com/Microsoft/Detours) examples.

    To demonstrate the reason for the slowdowns of Excel, we implemented the OfficeNoSleep.dll library, which intercepts OleSetClipboard and SleepEx calls.
    This library resets the dwMilliseconds param value of the SleepEx function from 200 to 0ms in each case where the previous call to OleSetClipboard(NULL) was unsuccessful.

    In our test example, the Range.Copy is executed 1000 times.
    Runtime statistics (without tracing WinApi calls):
      - Without using the OfficeNoSleep.dll library:
        - 1 instance of Excel 2007: 1.14 seconds.
        - 1 instance of Excel 2013: 1.45 seconds.
        - 4 instances of Excel 2007: 3.85, 3.26, 3.67 and 3.81 seconds.
        - 4 instances of Excel 2013: 13.17, 19.65, 25.79 and 25.73 seconds.
      - With the OfficeNoSleep.dll library:
        - 1 instance of Excel 2013: 1.48 seconds.
        - 4 instances of Excel 2013: 1.71, 2.67, 1.26 and 1.28 seconds.

    The test example, with the source of OfficeNoSleep.dll can be downloaded from link - https://techmill.ru/support/test.zip.
    The logs of WinApi trace (without using the OfficeNoSleep.dll library) can be downloaded from link - https://techmill.ru/support/trace_logs.zip (for convenience of analysis, data is also loaded into Excel files).  

    In the logs, look for OleSetClipboard(0) calls that return failures and subsequent SleepEx(c8,0) calls.
      - 2007, for example PID = 2136
      - 2013, for example PID = 9072

    Test case description:
      - test.xls
      - run_excel.vbs: executes the macro from test.xls in a separate instance of Excel
        Accepts parameters:
          - "trace": for logging WinApi calls (Excel loads the trcapi32.dll library)
          - "fix": fix Excel slowdown SleepEx 200 -> 0 (Excel loads the OfficeNoSleep.dll library)
      - test.vbs: runs the run_excel.vbs script simultaneously in several instances
        Accepts parameters:
          - 1st parameter specifies the required number of running instances
          - 2nd parameter - similar to run_excel.vbs

    Examples of commands:
       - just run 4 instances:
        cscript.exe test.vbs 4
       - run 4 instances with tracing into the syelogd.exe program (loads trcapi32.dll):
        cscript.exe test.vbs 4 trace
        -run 4 instances with "fix" of Excel slowdown (loads OfficeNoSleep.dll):
        cscript.exe test.vbs 4 fix

    Thanks.


    Saturday, November 10, 2018 6:30 PM

All replies

  • Are Microsoft employees looking at threads?
    If not, where is it better to send this information?

    Thanks!

    Wednesday, November 14, 2018 3:25 PM