none
Excel 2013 runs slower when parallelized over COM RRS feed

  • Question

  • We are seeing an issue that Excel 2013 runs slower when we start multiple copies to run in parallel. We have simplified this down to a VB script that attempts to use Excel to get and set a lot of values in 8 different spreadsheets. You can choose how many of the spreadsheets to load and run at once using different copies of Excel in parallel. The performance of Excel 2010 improves with parallel copies, but actually gets slower with Excel 2013.

    Performance of Excel 2010 vs 2013 using multiple processes

    The script which runs a single instance is as follows:

    Option Explicit
    
    Const xlCalculationManual = -4135
    Const xlWBATWorksheet = -4167
    
    Dim excel, workbook, worksheet, workbookPath
    Dim oldCalculation, oldDisplayAlerts
    Dim addin, rows, columns, formula, value1
    Dim value
    
    value = WScript.Arguments(0)
    
    Set excel = CreateObject("Excel.Application")
    'Create a new workbook
    Set workbook = excel.Workbooks.Add(xlWBATWorksheet)
    Set worksheet = excel.ActiveSheet
    
    'Reload all addins
    For Each addin in excel.Addins
       If addin.Installed Then
          addin.Installed = False
          addin.Installed = True
       End If
    Next
    
    'Set Excel settings
    oldCalculation = excel.Calculation
    excel.Calculation = xlCalculationManual
    oldDisplayAlerts = excel.DisplayAlerts
    excel.DisplayAlerts = False
    
    'Query row and column sizes
    Dim row, column
    For row = 1 To 267
       rows = worksheet.Range("B" & row).Rows.Count
       columns = worksheet.Range("B" & row).Columns.Count
    Next
    For column = 4 to 224
       rows = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Rows.Count
       columns = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Columns.Count
    Next
    
    'Set the values
    For row = 1 to 267
       formula = worksheet.Range("B" & row).Formula
       rows = worksheet.Range("B" & row).Rows.Count
       columns = worksheet.Range("B" & row).Columns.Count
       worksheet.Range("B" & row).Value = value
    Next
    For column = 4 to 224
       rows = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Rows.Count
       columns = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Columns.Count
       worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Value = value
    Next
    
    'Calculate
    excel.Calculation = oldCalculation
    
    'Query row and column sizes
    For row = 1 To 267
       rows = worksheet.Range("B" & row).Rows.Count
       columns = worksheet.Range("B" & row).Columns.Count
    Next
    For column = 4 to 224
       rows = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Rows.Count
       columns = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Columns.Count
    Next
    
    'Get the values
    For row = 1 To 267
       value1 = worksheet.Range("B" & row).Value
    Next
    For column = 4 to 224
       value1 = worksheet.Range(worksheet.Cells(2678, column), worksheet.Cells(2680, column)).Value
    Next
    
    excel.DisplayAlerts = oldDisplayAlerts
    
    'If you are not admin, you will need to change this.
    workbook.SaveAs "C:\testResults" & value & ".xlsx"
    
    workbook.Close False
    excel.Quit
    
    Set worksheet = Nothing
    Set workbook = Nothing
    Set excel = Nothing

    And the script which will run 8 copies of the previous script, optionally running them in parallel. Pass the number of Excels you want to run in parallel as a command line option.

    Option Explicit
    Dim numberOfProcesses, FSO, WshShell
    Dim t1, t2
    Dim i, j, k
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set WshShell = CreateObject("WScript.Shell")
    
    If WScript.Arguments.Count < 0 Then
       WScript.Echo "Missing Parameter: Please specify the number of Excel instances to run in parallel."
       WScript.Quit
    End If
    
    numberOfProcesses = WScript.Arguments(0)
    If numberOfProcesses < 1 Or numberOfProcesses > 8 Then
       WScript.Echo "Invalid Parameter: Please specify a number between 1 and 8."
       WScript.Quit
    End If
    
    WScript.Echo "Test will now run. A message box will display your results when it finishes."
    
    t1 = Timer
    'Run a total of 8 times.
    i = 0
    Do While i <> 8
       'Don't run more than we need to.
       If 8 - i - numberOfProcesses < 0 Then
          numberOfProcesses = numberOfProcesses - (8 - i - 1)
       End If
       
       'Run the processes.
       For j = 0 To numberOfProcesses - 1
          WshShell.Run "wscript ExcelPerformanceTest.vbs " & (i + j), 0, False
       Next
       
       'If you changed the path in the other script, change it here too.
       'Wait for all processes to finish.
       For k = i To i + numberOfProcesses - 1
          Do While FSO.FileExists("C:\testResults" & k & ".xlsx") <> True
             WScript.Sleep 100
          Loop
       Next
       i = i + numberOfProcesses
    Loop
    t2 = Timer
    WScript.Echo "Test Completed.  Time = " & (t2 - t1)

    Has anyone else run into this?

    Monday, October 26, 2015 9:18 PM

All replies

  • Re:  opening multiple workbooks

    XL 2013 opens each workbook in a separate window (SDI) and each window has its own Ribbon.
    Have you considered just opening the 8 workbooks and doing whatever you do? ...
    '---
    Sub TestExample()
     Dim N As Long
     Dim vNames As Variant

     vNames = Array("TestResult1s.xlsx", "TestResults2.xlsx", "TestResults3.xlsx", _
                    "TestResults4.xlsx", "TestResults5.xlsx", "TestResults6.xlsx", _
                    "TestResults7.xlsx", "TestResults8.xlsx")
     For N = 0 To 7
       Workbooks.Open vNames(N)
     Next
    End Sub
    '---
    [Edit]
    NOTE:  Avast is prompting me with a notice that the certificate is invalid when I click on "Phoenix Integration"
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 3:12 AM
    Tuesday, October 27, 2015 4:05 AM
  • Hi Phoenix,

    Based on the test, the performance of automation Excel 2013 is lower than Excel 2010. Specifically, there are lots of data reading/writing using Range object.

    To improve the performance, I suggest that you using array to reading/writing for the range object.

    Sub ReadWriteRangeWithArry()
    
    Dim Arr() As Variant
    Arr = Range("A1:A3")
    Dim R As Long
    Dim C As Long
    For R = 1 To UBound(Arr, 1)
        For C = 1 To UBound(Arr, 2)
            Debug.Print Arr(R, C)
        Next C
    Next R
    
    Range("B1:B3").Value = Arr
    End Sub
    

    In addition, here is an helpful article about imporving the performance of Excel:
    Excel 2010 Performance: Tips for Optimizing Performance Obstructions

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 27, 2015 8:24 AM
    Moderator
  • Hello and thanks for the response! Back in the day when this project was started, Excel behaved very badly if you attempted to use a single instance from multiple threads at the same time, giving out intermittent call failures because the Excel process is essentially single threaded for incoming COM calls. I will admit, though, that this fact is very dated. It would be worthy of trying out with Excel 2013 to see if it can handle it better.
    Tuesday, October 27, 2015 11:41 AM
  • Yes, you are right. Usually we do that, but in this case wanted a script that demonstrated the variability between Excel 2010 and Excel 2013. Thanks for the response!
    Tuesday, October 27, 2015 11:43 AM
  • I changed the runner script to do CreateObject() and the individual test scripts to use GetObject(,"Excel.Application") so that all the parallel threads are sharing the same instance of Excel. Unfortunately I am receiving error code 0x800AC472 on some of my threads. A brief online search shows this is an error Excel sends when it is "busy" and can't respond to an incoming OLE call. So it appears that using a single instance of Excel is still not viable, unfortunately.
    Tuesday, October 27, 2015 12:25 PM