[RESOLVED] C# Code running slower than molasses in January

[Vladamir]

I have a fairly simple C# project which reads in records from a MySql database and then transfers them over to an Excel file so some people in managment can see the fancy bar charts created therein with the data. I used to do something almost like this but it read a boat load of text files to populate the Excel file. And what appears to be happening here is the slowness of this is when the Excel file is being populated, not when the database operations are running. I thought at first it might be because the Excel file is on an obscure server out there on our LAN so I moved a copy of it over to my local C: drive. But again it's so darn slow I gotta believe there's something not right. Here is the code in my project:

using System;
using MySql.Data.MySqlClient;
using Microsoft.Office.Interop.Excel;

namespace AutoDwgReports
{
    class Program
    {
        private static Workbook vExcelWorkBook;
        private static Sheets vExcelWorkSheets;
        private static Application vExcelApp;

        static void Main(string[] args)
        {
            try
            {
                string path = @"C:\0\TempCharts.xls";
                DBFactory.OpenMySqlDB("MySql_Database", "My_UserName", "My_Passwd", "My_Database");
                MySqlDataReader Reader = null;
                MySqlCommand cmd = new MySqlCommand("SELECT email, timeStamp, hatchtype, retrofit, hss, nodbrecord FROM formInput;", DBFactory.conn);
                
                vExcelApp = new Application();
                vExcelApp.Visible = false;
                vExcelApp.DisplayAlerts = false;
                vExcelWorkBook = vExcelApp.Workbooks.Open(path);
                vExcelWorkSheets = vExcelWorkBook.Worksheets;
                Worksheet vExcelWorkSheet = (Worksheet)vExcelWorkBook.Worksheets["DATA"];
                string vHatchType = null;                 

                // setup the counter for the Excel file
                int vExcelCounter = 2806;

                Reader = cmd.ExecuteReader();
                while (Reader.Read())
                {
                    // Make an entry to the Excel file only if it was not a test drawing
                    if (Reader.GetInt16("nodbrecord") == 0)
                    {
                        vHatchType = Reader.GetString(2);
                        if (Reader.GetInt16(4) == 1) vHatchType = vHatchType + "SS";
                        if (Reader.GetInt16(3) == 1) vHatchType = vHatchType + "R"; 

                        vExcelWorkSheet.Range["A" + vExcelCounter].Value = Reader.GetString(0);
                        vExcelWorkSheet.Range["B" + vExcelCounter].Value = Reader.GetString(1);
                        vExcelWorkSheet.Range["C" + vExcelCounter].Value = vHatchType;
                        vExcelWorkSheet.Range["D" + vExcelCounter].Value = Reader.GetInt16(3);
                        vExcelWorkSheet.Range["E" + vExcelCounter].Value = Reader.GetInt16(4);
                        Console.WriteLine("Added " + vExcelCounter + " records.");
                        vExcelCounter++;
                    }
                }

                DBFactory.CloseDB();
                DBFactory.conn.Dispose();
                
                vExcelWorkBook.Save();
                vExcelWorkBook.Close();
                vExcelApp.Quit();

                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERROR: " + e.Message);
                Console.ReadKey();
            }
            finally
            {
            }
        }
    }
}

I watched this run using Gembox (dll in lieu of MS Excel Library for reference) yesterday at my house and it's faced as greased lightning. I also watched it run at my home using this same code and while it wasn't a lickety split like I saw with Gembox it still got done much faster than what I'm seeing here on my client's LAN. Gembox is limited unless you pay the $469 per seat.

[Lightning]

Isn't the problem that the charts are calculated after each cell you write? You could try to put the data in a empty worksheet, if that is fast, then you could copy-paste that data to the worksheet with the charts.

[Vladamir]

Okay, I double and triple checked this thing. Using the native Excel Library reference from M$ this it takes about 10 minutes for it to write all 1,500 records to the Excel file and for some strange reason I end up with a boat load of duplicate entries. Going to have to debug this some more. But I switch out with the Gembox.Spreadsheet reference and even though the free version is limited to 150 rows it completes it's work in the blink of an eye. Like I said I got some more debugging to do with this. I recall using this very similar method in VB.NET to read some text files and extract the same data out of there and it wrote thousands of records very fast. The MySql part of this runs in an instance and it's when it starts writing to the Excel file that is slows down to an arduous crawl.

Sorry Lightning...I didn't see your post until after I posted this. And I guess this just goes to show what happens when you walk away from a project for an extended period of time. I remember now that I had to turn off the autocalculate and the let the whole thing recalculate once it's all done. Thanks. My old senile brain is drifting away on thoughts of long legged Amazon women instead of how I once wrote this code in VB.

Oh yes and the reason the Gembox version was running very fast was that the free version is not only limited to 150 rows, it's limited to 5 sheets so I removed all the charts from that Excel file. DUH!...So with the inclusion of these two lines of code:

vExcelApp.Calculation = XlCalculation.xlCalculationManual;
.
.
.
vExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;

Everything is now working fast as a jack rabbit with a coyote on it's tail.

[Vladamir]

Now to move on to the next question I have on this. As you can see, I am breaking the Two-Dot rule all over the place. I did a quick review to see where I could get it to comply with the One-Dot only rule, but can't seem to figure that out. And if the Two-Dot rule is so important why is it that all, and I mean all of the examples I find out there, even the ones written by experienced M$ advisers routinely break the Two-Dot rule.