I have an interesting conundrum here, how do I quickly (under 1 minute) export a large datatable (filled from SQL, 35,000 rows) into an Excel spreadsheet for users. I have code in place that can handle the export, and while nothing is "wrong" with the code per se, it is infuriatingly slow taking 4 minutes to export the entire file (sometimes longer if a user has less RAM or is running more on their system). Sadly, this is an improvement over the 10+ minutes it used to take using our old method. Simply put, can this be made any faster, without using 3rd party components? If so, how? My code is as follows, the slow down occurs between messageboxes 6 and 7 where each row is written. Thank you all for taking the time to take a look at this:
Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnJeffTest.Click Test(MySPtoExport) End Sub Private Sub Test(ByVal SQL As String) 'Declare variables used to execute the VUE Export stored procedure MsgBox("start stop watch") Dim ConnectionString As New SqlConnection(CType(ConfigurationManager.AppSettings("ConnString"), String)) Dim cmdSP As New SqlClient.SqlCommand Dim MyParam As New SqlClient.SqlParameter Dim MyDataAdapter As New SqlClient.SqlDataAdapter Dim ExportDataSet As New DataTable Dim FilePath As String MsgBox("stop 1 - end of declare") Try ' open the connection ConnectionString.Open() ' Use the connection for this sql command cmdSP.Connection = ConnectionString 'set this command as a stored procedure command cmdSP.CommandType = CommandType.StoredProcedure 'get the stored procedure name and plug it in cmdSP.CommandText = SQL 'Add the Start Date parameter if required Select Case StDt Case Nothing ' there's no parameter to add Case Is = 0 ' there's no parameter to add Case Else 'add the parameter name, it's direction and its value MyParam = cmdSP.Parameters.Add("@StartDate", SqlDbType.VarChar) MyParam.Direction = ParameterDirection.Input MyParam.Value = Me.txtStartDate.Text End Select MsgBox("stop 2 - sql ready") 'Add the End Date parameter if required Select Case EdDt Case Nothing ' there's no parameter to add Case Is = 0 ' there's no parameter to add Case Else 'add the parameter name, it's direction and its value MyParam = cmdSP.Parameters.Add("@EndDate", SqlDbType.VarChar) MyParam.Direction = ParameterDirection.Input MyParam.Value = Me.txtEndDate.Text End Select 'Add the single parameter 1 parameter if required Select Case SPar1 Case Is = Nothing ' there's no parameter to add Case Is = "" ' there's no parameter to add Case Else 'add the parameter name, it's direction and its value MyParam = cmdSP.Parameters.Add(SPar1, SqlDbType.VarChar) MyParam.Direction = ParameterDirection.Input MyParam.Value = Me.txtSingleReportCrt1.Text End Select 'Add the single parameter 2 parameter if required Select Case Spar2 Case Is = Nothing ' there's no parameter to add Case Is = "" ' there's no parameter to add Case Else 'add the parameter name, it's direction and its value MyParam = cmdSP.Parameters.Add(Spar2, SqlDbType.VarChar) MyParam.Direction = ParameterDirection.Input MyParam.Value = Me.txtSingleReportCrt2.Text End Select MsgBox("stop 3 - params ready") 'Prepare the data adapter with the selected command MyDataAdapter.SelectCommand = cmdSP ' Set the accept changes during fill to false for the NYPDA export MyDataAdapter.AcceptChangesDuringFill = False 'Fill the Dataset tables (Table 0 = Exam Eligibilities, Table 1 = Candidates Demographics) MyDataAdapter.Fill(ExportDataSet) 'Close the connection ConnectionString.Close() 'refresh the destination path in case they changed it SPDestination = txtPDFDestination.Text MsgBox("stop 4 - procedure ran, datatable filled") Select Case ExcelFile Case True FilePath = SPDestination & lblReportName.Text & ".xls" Dim _excel As New Microsoft.Office.Interop.Excel.Application Dim wBook As Microsoft.Office.Interop.Excel.Workbook Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet wBook = _excel.Workbooks.Add() wSheet = wBook.ActiveSheet() Dim dt As System.Data.DataTable = ExportDataSet Dim dc As System.Data.DataColumn Dim dr As System.Data.DataRow Dim colIndex As Integer = 0 Dim rowIndex As Integer = 0 MsgBox("stop 5 - excel stuff declared") For Each dc In dt.Columns colIndex = colIndex + 1 _excel.Cells(1, colIndex) = dc.ColumnName Next MsgBox("stop 6 - Header written") For Each dr In dt.Rows rowIndex = rowIndex + 1 colIndex = 0 For Each dc In dt.Columns colIndex = colIndex + 1 _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) Next Next MsgBox("stop 7 - rows written") wSheet.Columns.AutoFit() MsgBox("stop 8 - autofit complete") Dim strFileName = SPDestination & lblReportName.Text & ".xls" If System.IO.File.Exists(strFileName) Then System.IO.File.Delete(strFileName) End If MsgBox("stop 9 - file checked") wBook.SaveAs(strFileName) wBook.Close() _excel.Quit() End Select MsgBox("File " & lblReportName.Text & " Exported Successfully!") 'Dispose of unneeded objects MyDataAdapter.Dispose() ExportDataSet.Dispose() StDt = Nothing EdDt = Nothing SPar1 = Nothing Spar2 = Nothing MyParam = Nothing cmdSP.Dispose() cmdSP = Nothing MyDataAdapter = Nothing ExportDataSet = Nothing Catch ex As Exception ' Something went terribly wrong. Warn user. MessageBox.Show("Error: " & ex.Message, "Stored Procedure Running Process ", _ MessageBoxButtons.OK, MessageBoxIcon.Error) Finally 'close the connection in case is still open If Not ConnectionString.State = ConnectionState.Closed Then ConnectionString.Close() ConnectionString = Nothing End If ' reset the fields ResetFields() End Try End Sub