CODEWITHSUNDEEP
c#sqlexcel
Sohaib Akhtar
Sohaib Akhtar

Reputation: 205

SQL reader takes a lot of time while creating excel file

I am now stuck in a process which is time consuming. I am creating an excel file using C# and I have a stored procedure in SQL which takes approximate 2-3 minutes to execute and return a result set of 90K lines. But when I am trying to execute the procedure in C# and reading the data in while loop it took me 30 minutes to create an excel file. Here's my Code

string gpn = cmbGPN.SelectedItem.ToString();
            string MRR_Query = " exec sp_MRR_Retention '"+Yr+"','"+Mn+"'";

            int xlCol, xlRow, xlMin, xlMax;

            reader = func.getReader(MRR_Query);

            object typeMissing = Type.Missing;
            Excel._Application app = new Excel.Application();
            Excel.Workbook workbook = app.Workbooks.Add(typeMissing);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
            object misValue = System.Reflection.Missing.Value;
            sheet.Name = "MRR Retention";

            app.ActiveWindow.Zoom = 80;
            sheet.Application.ActiveWindow.SplitRow = 1;
            sheet.Application.ActiveWindow.FreezePanes = true;

            // Column name for raw data sheet
            sheet.Cells[1, 1] = "Month";
            sheet.Cells[1, 2] = "Parent_Name";
            sheet.Cells[1, 3] = "Customer_Name";
            sheet.Cells[1, 4] = "Customer_Account_No";
            sheet.Cells[1, 5] = "Item_Category";
            sheet.Cells[1, 6] = "Item_Description_Summary";
            sheet.Cells[1, 7] = "Item_Number";
            sheet.Cells[1, 8] = "Date_Range";
            sheet.Cells[1, 9] = "Activity_Type";
            sheet.Cells[1, 10] = "Line_Type";
            sheet.Cells[1, 11] = "IBX_Code";
            sheet.Cells[1, 12] = "IBX_Country";
            sheet.Cells[1, 13] = "Primary_Sales_Rep";
            sheet.Cells[1, 14] = "MRC_Amount_USD_Budget_Rate";
            sheet.Cells[1, 15] = "Entered_Currency_Code";
            sheet.Cells[1, 16] = "MRC_Amount_LC";
            sheet.Cells[1, 17] = "UCM_ID";
            sheet.Cells[1, 18] = "GAM_TAG";
            sheet.Cells[1, 19] = "Client_Services_Manager";
            sheet.Cells[1, 20] = "Sales_Program_Type";
            sheet.Cells[1, 21] = "SFDC_Account_ID";
            sheet.Cells[1, 22] = "Account_Owner";

            sheet.Range["A1:W1"].Borders.Color = Color.Black;
            sheet.Range["A1:W1"].ColumnWidth = 12;
            sheet.Range["A1:W1"].Interior.Color = Color.YellowGreen;
            sheet.Range["A1:W1"].Font.Color = Color.Black;
            sheet.Range["A1:W1"].Font.Bold = true;
            sheet.Range["A1:W1"].EntireRow.AutoFit();


            int row = 2;

            while (reader.Read())
            {
                sheet.Cells[row, 1] = reader.GetValue(0);
                sheet.Cells[row, 2] = reader.GetValue(1);
                sheet.Cells[row, 3] = reader.GetValue(2);
                sheet.Cells[row, 4] = reader.GetValue(3);
                sheet.Cells[row, 5] = reader.GetValue(4);
                sheet.Cells[row, 6] = reader.GetValue(5);
                sheet.Cells[row, 7] = reader.GetValue(6);
                sheet.Cells[row, 8] = reader.GetValue(7);
                sheet.Cells[row, 9] = reader.GetValue(8);
                sheet.Cells[row, 10] = reader.GetValue(9);
                sheet.Cells[row, 11] = reader.GetValue(10);
                sheet.Cells[row, 12] = reader.GetValue(11);
                sheet.Cells[row, 13] = reader.GetValue(12);
                sheet.Cells[row, 14] = reader.GetValue(13);
                sheet.Cells[row, 15] = reader.GetValue(14);
                sheet.Cells[row, 16] = reader.GetValue(15);
                sheet.Cells[row, 17] = reader.GetValue(16);
                sheet.Cells[row, 18] = reader.GetValue(17);
                sheet.Cells[row, 19] = reader.GetValue(18);
                sheet.Cells[row, 20] = reader.GetValue(19);
                sheet.Cells[row, 21] = reader.GetValue(20);
                sheet.Cells[row, 22] = reader.GetValue(21);

                row = row + 1;
            }

            reader.Close();                                                                             //closing the reader and nullify if any recordset is remaining 
            func.CloseCon();

            workbook.SaveAs("D:\\MRR_Retention_Auto.xlsx", typeMissing, typeMissing, typeMissing, typeMissing, typeMissing, Excel.XlSaveAsAccessMode.xlExclusive, typeMissing, typeMissing, typeMissing, typeMissing, typeMissing);
            workbook.Close(true, typeMissing, typeMissing);
            app.Quit();

Can anyone please look out in this matter and tell me how to make this process fast. Will it be fast if I use SQL Data Adapter instead ? Looking forward for suggestions and answers.

Upvotes: 1

Views: 106

Answers (1)

Hambone
Hambone

Reputation: 16397

If you really want speed (and compact code), let Excel do all of the heavy lifting for you and use Microsoft Query (built into Excel) instead of doing this by hand. Here is a sample of how you would take a query and convert it into an Excel table linked to MS Query:

string sql = "select * from foo";
string source = "your connection string here";

Excel.ListObject lo = sheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcQuery,
    source, true, Excel.XlYesNoGuess.xlGuess, range);

try
{
    lo.QueryTable.CommandText = sql;
    lo.Refresh();
}
catch (Exception ex)
{
    ErrorMessage = ex.ToString();
}

range would be an Excel Range object containing the destination of the top left cell where you want the output.

If you don't know what your connection string looks like, the best way to decompose this is to generate a query through Excel and then go into the query properties, copy and paste the connection string text.

Also, if your connection is ODBC, it's sufficient to say "DNS=<dns name>;" and Excel will pull all of the connection properties from the ODBC properties.

Upvotes: 1

Related Questions