Results 1 to 5 of 5

Thread: Slow Export of SQL View to Excel

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2015
    Posts
    111

    Slow Export of SQL View to Excel

    Hi, there. I have two questions. I'm using SQL Server as my database. I'm exporting a view into an Excel spreadsheet. I have two questions about it.

    #1. My export is working. But the only thing is, it seems really slow. I'm thinking I might be doing it the slowest way possible. But from what I have looked up, it appears I'm doing it the way others suggest. The Excel sheet has 694 rows and 30 columns. It takes about 50-60 seconds to export. It seems like it should be faster since there's not that much data. Is there a way to export it quicker? My code:

    Code:
     Dim connetionExport As String = Nothing
            Dim SQLconnectionExport As SqlConnection
            Dim commandExport As SqlCommand
            Dim adapterExport As New SqlDataAdapter()
            Dim dtExport As New DataTable
            Dim sqlExport As String = Nothing
    
            Dim ExcelApp As Excel.Application
            Dim ExcelWorkBk As Excel.Workbook
            Dim ExcelWorkSht As Excel.Worksheet
    
            ExcelApp = New Excel.Application
            ExcelWorkBk = ExcelApp.Workbooks.Add()
            ExcelWorkSht = ExcelWorkBk.Sheets("Sheet1")
    
    
            Try
                connetionExport = "Data Source=LookData1;Initial Catalog=IN_OrderLog;Trusted_Connection=true"
    
                sqlExport = "SELECT * FROM viewActiveDealers"
    
                SQLconnectionExport = New SqlConnection(connetionExport)
                SQLconnectionExport.Open()
                commandExport = New SqlCommand(sqlExport, SQLconnectionExport)
                adapterExport.SelectCommand = commandExport
                adapterExport.Fill(dtExport)
                adapterExport.Dispose()
                commandExport.Dispose()
                SQLconnectionExport.Close()
    
                '   ExcelApp.Visible = True
    
                'First export the column headres
                For h As Integer = 0 To dtExport.Columns.Count - 1
                    ExcelWorkSht.Cells(1, h + 1) = dtExport.Columns(h).ColumnName
                Next
    
                'Now export all the data, looping thru rows and columns
                For r As Integer = 0 To dtExport.Rows.Count - 1
                    For c As Integer = 0 To dtExport.Columns.Count - 1
                        ExcelWorkSht.Cells(r + 2, c + 1) = dtExport.Rows(r).ItemArray(c)
                    Next
                Next
    
                With ExcelApp
                    .Visible = True
                    .Rows("1:1").Font.FontStyle = "Bold"
                    .Columns.AutoFit()
    
                End With
    #2. I want to keep the Excel sheet open after the export is complete. I've seen the code during my research were others are quitting and releasing the Excel objects. If I'm keeping Excel open, should I be releasing? I can look it up if the answer is yes. I'm not looking for how to do it. Just if I am.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Slow Export of SQL View to Excel

    If you want to keep Excel open, then don't be releasing the Excel objects. That's all about dispensing with Excel once you are done with it. Eventually, you WILL want to do that, but not until you are done with Excel.

    As to the speed, you're right that it should be FAR faster. You can use a Stopwatch object to time different parts to figure out EXACTLY what is slow, but there are really only two candidates in the code you showed, and likely only one. So, you can add a Stopwatch object, start it before that for loop, end it afterwards, and look at ElapsedMilliseconds. You'll likely find that's virtually the WHOLE slowdown right there. If that isn't it, then it might be that With block after the for loop. I'm not sure what Excel does for changing fonts and AutoFit. It's possible those could be somewhat slow, but not very likely.
    My usual boring signature: Nothing

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Slow Export of SQL View to Excel

    this is for a Access you will have to adapt for your SQL Database

    Code:
    Option Strict On
    
    Imports System.Data.OleDb
    
    
    Public Class Form2
    
       
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
            Dim sSql As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\database1.accdb;Persist Security Info=False")
            Dim Filename As String
    
            Filename = "E:\Berichte\TestExcel.xlsx"
    
            sSql = "Select * " & _
             "Into [EmployeeList] In '" & Filename & "' 'EXCEL 12.0 XML;' " & _
             "From Kunden Order By Firma"
    
            'take a look at your Sql
            MsgBox(sSql)
    
            con.Open()
            ExecuteSQL(con, sSql)
            con.Close()
            con = Nothing
            Process.Start(Filename) 'start and show Excel file
        End Sub
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                     ByVal sSQL As String, _
                                     Optional ByRef ErrMessage As String = Nothing, _
                                     Optional ByVal TransAction As  _
                                     OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    
    End Class
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Slow Export of SQL View to Excel

    If the Excel type is .xlsx look at SpreadSheetLight installed via NuGet Package Manager.

    Code:
    ''' <summary>
    ''' Extremely simply import.
    ''' </summary>
    ''' <param name="pFileName">Excel file to import into</param>
    ''' <param name="pSheetName">Sheet in Excel file to import into</param>
    ''' <param name="pDataTable">DataTable to import</param>
    ''' <param name="pColumnHeaders">Use column headers or not</param>
    Public Sub SimpleExportRaw(
        pFileName As String,
        pSheetName As String,
        pDataTable As DataTable,
        pColumnHeaders As Boolean)
    
        Using doc As New SLDocument()
            doc.SelectWorksheet(pSheetName)
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
            doc.SaveAs(pFileName)
        End Using
    
    End Sub

  5. #5
    PowerPoster
    Join Date
    Mar 2002
    Location
    UK
    Posts
    4,780

    Re: Slow Export of SQL View to Excel

    http://www.eident.co.uk/2016/03/top-...your-vba-code/ these tips follow the same for external code. Turn off the display updates and calculations and perform changes on ranges instead of individual cells.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width