-
Apr 10th, 2020, 01:04 PM
#1
Thread Starter
Lively Member
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.
-
Apr 10th, 2020, 01:43 PM
#2
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
-
Apr 10th, 2020, 02:31 PM
#3
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.
-
Apr 10th, 2020, 04:19 PM
#4
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
-
Apr 10th, 2020, 07:53 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|