none
SQL datafill takes forever RRS feed

  • Question

  • Hello,

    I have a button that will get results between 2 dates and should export them to Excel.

    It works fine except the entire process takes 15 to 20 minutes for 20+ thousand rows where on in SQL Management Studio it takes 15 seconds.

    What am I doing wrong in my code for this horrible performance?

                SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
                dscmd.SelectCommand.CommandTimeout = 900;
                DataSet ds = new DataSet();
                dscmd.Fill(ds);

                progressBar2.Maximum = ds.Tables[0].Rows.Count;

                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    progressBar2.Value = i;
                    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                    {
                        if (i == 0)
                        {
                            data = ds.Tables[0].Columns[j].ColumnName.ToString();
                            xlWorkSheet.Cells[i + 1, j + 1] = data;
                        }
                        else
                        {
                            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                            xlWorkSheet.Cells[i + 1, j + 1] = data;

                        }
                    }
                }

                

                xlWorkBook.SaveAs("c:\\Indications.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,   Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created , you can find the file c:\\Indications.xls");
                progressBar2.Value = 0;

    Monday, March 25, 2019 4:33 PM

All replies

  • Hello,

    First off Excel automation is slower than using 

    • A SQL SELECT INTO statement to go from database to Excel 
    • Using OpenXML for Excel (downside is time to learn).

    Both of the above are lightweight solutions and if had to guess SQL-Server is using OpenXML but only Microsoft would know this.

    Bottom line is Excel automation is slower then the two options above.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 25, 2019 7:15 PM
    Moderator
  • The well known method to speed up your loops is to write all of the value to an array of objects. Maybe like in this simple approach:

    var dt = ds.Tables[0];

     

    object[,] values = new object[dt.Rows.Count, dt.Columns.Count];

     

    for( int i = 0; i < dt.Rows.Count; ++i )

    {

        for( int j = 0; j < dt.Columns.Count; ++j )

        {

            values[i, j] = dt.Rows[i][j];

        }

    }

     

    Then assign this array to worksheet using single line:

    r.Value2 = values;

     

    where r is the target Range.

    (The order of data in array can be checked experimentally).


    If the amount of data is large, you can split it into portions.


    • Edited by Viorel_MVP Monday, March 25, 2019 7:56 PM
    Monday, March 25, 2019 7:46 PM
  • I managed to do it by first writing it to XML file then open the XML file and convert it to xlsx file, was very fast.

    SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
                dscmd.SelectCommand.CommandTimeout = 900;
                DataSet ds = new DataSet();
                DataTable dt = null;
                dscmd.Fill(ds);
                
                dt = ds.Tables[0];
                dt.Columns[1].DateTimeMode = DataSetDateTime.Unspecified;
                try
                {
                    if (File.Exists("C:\\IndicationsXML.xml"))
                    {
                        File.Delete("C:\\IndicationsXML.xml");
                    }
                }
                catch { }

                dt.WriteXml("C:\\IndicationsXML.xml");
                Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook excelWorkBook = xApp.Workbooks.OpenXML("C:\\IndicationsXML.xml", Type.Missing, Microsoft.Office.Interop.Excel.XlXmlLoadOption.xlXmlLoadImportToList);
                try
                {
                    if (File.Exists("C:\\Indications.xlsx"))
                    {
                        File.Delete("C:\\Indications.xlsx");
                    }
                    excelWorkBook.SaveAs("C:\\Indications.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange);
                    excelWorkBook.Close();
                    xApp.Workbooks.Close();
                }
                catch
                {
                    MessageBox.Show("Indications.xlsx is in use, please close and try again!");
                    excelWorkBook.Close();
                    xApp.Workbooks.Close();
                }

    Tuesday, March 26, 2019 1:59 PM