none
Performance degradation when spawning multiple instance of EXCEL.EXE using C# RRS feed

  • Question

  • I use a C# program to spawn 40 instances of Excel to run some data processing. I call a VBA function inside EXCEL. Whenever the 16th instance of the EXCEL.EXE is spawned, I can see a drastic drop of speed in the EXCEL data processing.

    This is the code I use to spawn EXCEL in C#:

    namespace ExcelDriver
    {
        public partial class frmExcel : Form
        {
            public frmExcel()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                Thread[] threads = new Thread[100];
                Cursor.Current = Cursors.WaitCursor;
                string cond = string.Empty;
                string param = string.Empty;
                string inCond = string.Empty;
                string inParam = string.Empty;
    
                for (int i = 0; i < Convert.ToInt32(ConfigurationManager.AppSettings["noofxl"]); ++i)
                {
                    cond = string.Empty;
                    cond = string.Concat("Cond", (i + 1).ToString());
                    param = string.Empty;
                    param = string.Concat("Param", (i + 1).ToString());
                    inCond = string.Empty;
                    inCond = ConfigurationManager.AppSettings[cond];
                    inParam = string.Empty;
                    inParam = ConfigurationManager.AppSettings[param];
                    threads[i] = new Thread(() => XLThreadProc(inCond,inParam, i));
                    threads[i].Name = string.Concat("Threads",(i+1).ToString());
                    threads[i].Start();
                    Thread.Sleep(Convert.ToInt32(ConfigurationManager.AppSettings["sleep"]));
                }
    
                Cursor.Current = Cursors.Default;
    
                MessageBox.Show("The calculations are complete", "My Application",MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
    
            private static void XLThreadProc(string inCond, string inParam, int intLoop)
            {
                string fname = "";
                object misValue = System.Reflection.Missing.Value;
    
                Excel.Application xlApp = new Excel.Application();
    
                foreach (Excel.AddIn myAddIn in xlApp.AddIns)
                {
                    if (myAddIn.Name == ConfigurationManager.AppSettings["addinfilename"])
                    {
                        myAddIn.Installed = false;
                        myAddIn.Installed = true;
                    }
                }
    
                Excel.Workbook xlWb;
    
                xlWb = xlApp.Workbooks.Open(ConfigurationManager.AppSettings["excelpath"], 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
                fname = ConfigurationManager.AppSettings["functionname"];
    
                xlApp.Run(fname, inCond, inParam);
    
                xlWb.Close(false, misValue, misValue);
    
                xlApp.Quit();
    
                releaseObject(xlWb);
                releaseObject(xlApp);
            }
    
            private static void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }
    }


    The VBA function did a loop and write a random number to cell A1. I then refer cell B3 to cell A1, cell C4 to cell B3, and cell D5 to cell C4. This is a create a cascading update of calculation to the cells. Every 10000th loop, I will log the timestamp (TS). I spawn 40 instances, with a sleep of 3 seconds between every instance spawn.

    Public Sub RunBatch(ByVal strCond As String, ByVal strParam As String)
    
        Dim ws As Worksheet
        Dim i As Long
        Dim objFSO As FileSystemObject: Set objFSO = New FileSystemObject
        Dim objTextStream As TextStream
        
        Set objTextStream = objFSO.OpenTextFile(strCond, ForAppending, True)
        objTextStream.WriteLine "Time created: " & Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
        
        Application.ScreenUpdating = False
        
        Set ws = Worksheets("Sheet1")
        
        For i = 1 To 1500000
            ws.Range("A1").Value = Rnd(DateTime.Now)
            If i Mod 10000 = 0 Then
                objTextStream.WriteLine "i = " & i & "; TS = " & Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
            End If
        Next i
        
        objTextStream.Close
        Set objTextStream = Nothing
        Set objFSO = Nothing
        Set ws = Nothing
        Application.ScreenUpdating = True
    
    End Sub



    I have the log file entries as below (TS = time stamp):

    Instance 01 Time created: 2017-10-18 15:55:21
    i = 10000; TS = 2017-10-18 15:55:23
    Instance 02 Time created: 2017-10-18 15:55:24
    i = 20000; TS = 2017-10-18 15:55:24
    i = 30000; TS = 2017-10-18 15:55:26
    i = 40000; TS = 2017-10-18 15:55:27
    Instance 03 Time created: 2017-10-18 15:55:28
    i = 50000; TS = 2017-10-18 15:55:29
    i = 60000; TS = 2017-10-18 15:55:30
    Instance 04 Time created: 2017-10-18 15:55:31
    i = 70000; TS = 2017-10-18 15:55:32
    Instance 05 Time created: 2017-10-18 15:55:34
    i = 80000; TS = 2017-10-18 15:55:34
    i = 90000; TS = 2017-10-18 15:55:36
    i = 100000; TS = 2017-10-18 15:55:37
    Instance 06 Time created: 2017-10-18 15:55:38
    i = 110000; TS = 2017-10-18 15:55:39
    Instance 07 Time created: 2017-10-18 15:55:41
    i = 120000; TS = 2017-10-18 15:55:41
    i = 130000; TS = 2017-10-18 15:55:43
    Instance 08 Time created: 2017-10-18 15:55:44
    i = 140000; TS = 2017-10-18 15:55:46
    Instance 09 Time created: 2017-10-18 15:55:48
    i = 150000; TS = 2017-10-18 15:55:49
    Instance 10 Time created: 2017-10-18 15:55:51
    i = 160000; TS = 2017-10-18 15:55:52
    Instance 11 Time created: 2017-10-18 15:55:54
    i = 170000; TS = 2017-10-18 15:55:55
    Instance 12 Time created: 2017-10-18 15:55:57
    i = 180000; TS = 2017-10-18 15:55:59
    Instance 13 Time created: 2017-10-18 15:56:00
    Instance 14 Time created: 2017-10-18 15:56:03
    i = 190000; TS = 2017-10-18 15:56:04
    Instance 15 Time created: 2017-10-18 15:56:07
    Instance 16 Time created: 2017-10-18 15:56:10
    i = 200000; TS = 2017-10-18 15:56:11
    Instance 17 Time created: 2017-10-18 15:56:13
    Instance 18 Time created: 2017-10-18 15:56:16
    Instance 19 Time created: 2017-10-18 15:56:19
    Instance 20 Time created: 2017-10-18 15:56:24
    i = 210000; TS = 2017-10-18 15:56:25
    i = 220000; TS = 2017-10-18 15:56:32
    i = 230000; TS = 2017-10-18 15:56:38
    i = 240000; TS = 2017-10-18 15:56:44
    i = 250000; TS = 2017-10-18 15:56:51
    i = 260000; TS = 2017-10-18 15:56:58
    i = 270000; TS = 2017-10-18 15:57:04
    i = 280000; TS = 2017-10-18 15:57:10
    i = 290000; TS = 2017-10-18 15:57:16
    i = 300000; TS = 2017-10-18 15:57:23
    i = 310000; TS = 2017-10-18 15:57:29
    i = 320000; TS = 2017-10-18 15:57:36
    i = 330000; TS = 2017-10-18 15:57:43
    i = 340000; TS = 2017-10-18 15:57:49
    i = 350000; TS = 2017-10-18 15:57:56
    i = 360000; TS = 2017-10-18 15:58:02
    i = 370000; TS = 2017-10-18 15:58:09
    i = 380000; TS = 2017-10-18 15:58:15
    i = 390000; TS = 2017-10-18 15:58:22
    i = 400000; TS = 2017-10-18 15:58:29
    i = 410000; TS = 2017-10-18 15:58:35
    i = 420000; TS = 2017-10-18 15:58:42
    i = 430000; TS = 2017-10-18 15:58:48
    i = 440000; TS = 2017-10-18 15:58:55
    i = 450000; TS = 2017-10-18 15:59:01
    i = 460000; TS = 2017-10-18 15:59:08
    i = 470000; TS = 2017-10-18 15:59:15
    i = 480000; TS = 2017-10-18 15:59:21
    i = 490000; TS = 2017-10-18 15:59:28
    i = 500000; TS = 2017-10-18 15:59:34
    i = 510000; TS = 2017-10-18 15:59:41
    i = 520000; TS = 2017-10-18 15:59:48
    i = 530000; TS = 2017-10-18 15:59:54
    i = 540000; TS = 2017-10-18 16:00:01
    i = 550000; TS = 2017-10-18 16:00:07
    i = 560000; TS = 2017-10-18 16:00:14
    i = 570000; TS = 2017-10-18 16:00:21
    i = 580000; TS = 2017-10-18 16:00:27
    i = 590000; TS = 2017-10-18 16:00:34
    i = 600000; TS = 2017-10-18 16:00:41
    i = 610000; TS = 2017-10-18 16:00:47
    i = 620000; TS = 2017-10-18 16:00:54
    i = 630000; TS = 2017-10-18 16:01:00
    i = 640000; TS = 2017-10-18 16:01:07
    i = 650000; TS = 2017-10-18 16:01:14
    i = 660000; TS = 2017-10-18 16:01:20
    i = 670000; TS = 2017-10-18 16:01:27
    i = 680000; TS = 2017-10-18 16:01:34
    i = 690000; TS = 2017-10-18 16:01:40
    i = 700000; TS = 2017-10-18 16:01:47
    i = 710000; TS = 2017-10-18 16:01:53
    i = 720000; TS = 2017-10-18 16:02:00
    i = 730000; TS = 2017-10-18 16:02:07
    i = 740000; TS = 2017-10-18 16:02:13
    i = 750000; TS = 2017-10-18 16:02:20
    i = 760000; TS = 2017-10-18 16:02:27
    i = 770000; TS = 2017-10-18 16:02:33
    i = 780000; TS = 2017-10-18 16:02:40
    i = 790000; TS = 2017-10-18 16:02:46
    i = 800000; TS = 2017-10-18 16:02:53
    i = 810000; TS = 2017-10-18 16:03:00
    i = 820000; TS = 2017-10-18 16:03:06
    i = 830000; TS = 2017-10-18 16:03:13
    i = 840000; TS = 2017-10-18 16:03:19
    i = 850000; TS = 2017-10-18 16:03:26
    i = 860000; TS = 2017-10-18 16:03:33
    i = 870000; TS = 2017-10-18 16:03:39
    i = 880000; TS = 2017-10-18 16:03:46
    i = 890000; TS = 2017-10-18 16:03:53
    i = 900000; TS = 2017-10-18 16:03:59
    i = 910000; TS = 2017-10-18 16:04:06
    i = 920000; TS = 2017-10-18 16:04:12
    i = 930000; TS = 2017-10-18 16:04:19
    i = 940000; TS = 2017-10-18 16:04:25
    i = 950000; TS = 2017-10-18 16:04:32
    i = 960000; TS = 2017-10-18 16:04:39
    i = 970000; TS = 2017-10-18 16:04:45
    i = 980000; TS = 2017-10-18 16:04:52
    i = 990000; TS = 2017-10-18 16:04:58
    i = 1000000; TS = 2017-10-18 16:05:05
    i = 1010000; TS = 2017-10-18 16:05:12
    i = 1020000; TS = 2017-10-18 16:05:18
    i = 1030000; TS = 2017-10-18 16:05:25
    i = 1040000; TS = 2017-10-18 16:05:31
    i = 1050000; TS = 2017-10-18 16:05:38
    i = 1060000; TS = 2017-10-18 16:05:45
    i = 1070000; TS = 2017-10-18 16:05:51
    i = 1080000; TS = 2017-10-18 16:05:58
    i = 1090000; TS = 2017-10-18 16:06:04
    i = 1100000; TS = 2017-10-18 16:06:11
    i = 1110000; TS = 2017-10-18 16:06:17
    i = 1120000; TS = 2017-10-18 16:06:24
    i = 1130000; TS = 2017-10-18 16:06:31
    i = 1140000; TS = 2017-10-18 16:06:37
    i = 1150000; TS = 2017-10-18 16:06:44
    i = 1160000; TS = 2017-10-18 16:06:50
    i = 1170000; TS = 2017-10-18 16:06:57
    i = 1180000; TS = 2017-10-18 16:07:04
    i = 1190000; TS = 2017-10-18 16:07:10
    i = 1200000; TS = 2017-10-18 16:07:17
    i = 1210000; TS = 2017-10-18 16:07:23
    i = 1220000; TS = 2017-10-18 16:07:30
    i = 1230000; TS = 2017-10-18 16:07:36
    i = 1240000; TS = 2017-10-18 16:07:43
    i = 1250000; TS = 2017-10-18 16:07:50
    i = 1260000; TS = 2017-10-18 16:07:56
    i = 1270000; TS = 2017-10-18 16:08:03
    i = 1280000; TS = 2017-10-18 16:08:09
    i = 1290000; TS = 2017-10-18 16:08:16
    i = 1300000; TS = 2017-10-18 16:08:22
    i = 1310000; TS = 2017-10-18 16:08:29
    i = 1320000; TS = 2017-10-18 16:08:35
    i = 1330000; TS = 2017-10-18 16:08:42
    i = 1340000; TS = 2017-10-18 16:08:49
    i = 1350000; TS = 2017-10-18 16:08:55
    i = 1360000; TS = 2017-10-18 16:09:02
    i = 1370000; TS = 2017-10-18 16:09:08
    i = 1380000; TS = 2017-10-18 16:09:15
    i = 1390000; TS = 2017-10-18 16:09:22
    i = 1400000; TS = 2017-10-18 16:09:28
    i = 1410000; TS = 2017-10-18 16:09:35
    i = 1420000; TS = 2017-10-18 16:09:41
    i = 1430000; TS = 2017-10-18 16:09:48
    i = 1440000; TS = 2017-10-18 16:09:54
    i = 1450000; TS = 2017-10-18 16:10:01
    i = 1460000; TS = 2017-10-18 16:10:08
    i = 1470000; TS = 2017-10-18 16:10:14
    i = 1480000; TS = 2017-10-18 16:10:21
    i = 1490000; TS = 2017-10-18 16:10:27
    i = 1500000; TS = 2017-10-18 16:10:34

    This program is run in a AWS server that has 40 vCPU with 160GB RAM.

    Any guidance on what could be wrong is appreciated.

    Wednesday, October 18, 2017 6:27 AM

All replies

  • Hi Colin.Chin,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, October 19, 2017 8:34 AM
  • Hello,

    Do you check each Excel instance could be successfully released? I test your code for one thread. The Excel instance could not be closed. I suggest use the following code to make sure each object would be released.

            private static void XLThreadProc()
            {
                object misValue = System.Reflection.Missing.Value;
    
                Excel.Application xlApp = new Excel.Application();
                Excel.AddIns addIns;
                addIns = xlApp.AddIns;
      
                foreach (Excel.AddIn myAddIn in addIns)
                {
                    if (myAddIn.Name == "Book1.xlam")
                    {
                        myAddIn.Installed = false;
                        myAddIn.Installed = true;
                    }
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(myAddIn);
    
                }
    
                Excel.Workbooks xlWbs;
                Excel.Workbook xlWb;
                xlWbs = xlApp.Workbooks;
                xlWb = xlWbs.Open(@"D:\test.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
                xlApp.Run("test");
    
                xlWb.Close(false);
    
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWb);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWbs);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(addIns);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
    
            }

    The following link might help to improve performance:

    Top 10 List of Performance Issues in Excel Workbooks

    Improving Performance in Excel 2007

    Besides, I suggest you create a VSTO add-in or XLL add-in. I think they would be faster than vba add-ins. 

    Getting Started Programming VSTO Add-ins

    Welcome to the Excel Software Development Kit

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, October 20, 2017 7:04 AM
    Moderator