Solved

MS SQL to VBA-Excel large data dump

Posted on 2015-01-14
6
157 Views
Last Modified: 2015-01-22
Need to insert 250 000 records into Excel sheet from MS SQL DB.
I use VBA ADODB connection.
I do it frequenty.
Now it takes to run almost 4 minutes, way too long.

Here is my Sub, any way to speed it up?

'Query DB and populate data in specified worksheet starting row/Col in active workbook
Sub SQLSrvDB_LowLevel_ReadRecSet(strSQL As String, strWksh As String, intstartRow As Integer, intStartCol As Integer)

  Dim time1, time2, time3, time4    'Used for timing response time
  Dim destRng As Range
  
  'Instantiate Record Set
  Set privADORecSet = New ADODB.Recordset
  
  'Query DB
  On Error Resume Next
  time1 = Time
  privADORecSet.Open Source:=strSQL, ActiveConnection:=privADOConnDB, CursorType:=adOpenStatic, LockType:=adLockReadOnly
  If Err.Number <> 0 Then MsgBox Err.Description
  time2 = Time
  
  'Populate RawData worksheet
  time3 = Time
  Set destRng = Range(Worksheets(strWksh).Cells(intstartRow, intStartCol), _
                      Worksheets(strWksh).Cells(privADORecSet.RecordCount, privADORecSet.Fields.Count - 1))
  destRng.CopyFromRecordset privADORecSet
  time4 = Time
  
  'Close the objects
  privADORecSet.Close
  
  'Recover memory
  Set privADORecSet = Nothing
  Set destRng = Nothing
  
End Sub

Open in new window

0
Comment
Question by:CABRLU63
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40550173
I assume you've done the obvious and set application.calculation =false before you start?

you might save a bit of time by just specifying the starting call for the .copyfromrecordset, so that it's not evaluating the whole recordset before it starts copying...

'set destRng to a single cell range
Set destRng = Range(Worksheets(strWksh).Cells(intstartRow, intStartCol),Worksheets(strWksh).Cells(intstartRow, intStartCol),))
  destRng.CopyFromRecordset privADORecSet

Open in new window


Have you compared the length of time it takes to push the same set of data using the data import/export wizard?

Have you tried writing the data to a file on the local hard drive of the SQL server?

I write large recordsets (containing a lot of varchar columns) and it does take a while, especially to network shares, but 4 minutes for 250k records is a bit slow.

How large is the resulting file and how fast is your LAN?
0
 
LVL 10

Expert Comment

by:Ray
ID: 40550197
Have you connected to your sql server with a query tool like SQL Server Management studio and run your query there?  If not, do so and see how long it takes.  If the times are within a few seconds, the bottleneck is your SQL Server.  If it is fast through query tool, then the issue is likely within your VBA and excel as 'Simon' mentioned.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40551036
Just spotted a typo in my earlier comment...
you might save a bit of time by just specifying the starting call for the .copyfromrecordset, so that it's not evaluating the whole recordset before it starts copying...
Should read
you might save a bit of time by just specifying the starting cell for the .copyfromrecordset, so that it's not evaluating the whole recordset before it starts copying...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:CABRLU63
ID: 40552342
Yeah, same dataset runs for 20 seconds in MS SQL Server.

I've added your code and it sped up things by 11%.

I'm on 2 mbps down, and 4 mbps up network.

I don't believe I can put that data on the server's drive.

Output file is 77 MB large.

I'm actually inserting that data into the tab with RawData and then later my code refreshes 10 Pivots.
0
 
LVL 10

Assisted Solution

by:Ray
Ray earned 250 total points
ID: 40552382
When you ran it via MS SQL Server did you do that from a local machine (where you're running the excel) or did you do it directly on the same box as the SQL Server?  I ask because at 2mbps down, it should take a while to receive 77MB worth of data at your workstation.  To simply download 77Mb it would take over 4 1/2 minutes at that speed.  I'm sure it is not quite that much actual data since there is overhead in the file size, hence why it only takes 4 minutes.

Sorry if I'm 'barking up the wrong tree' here.
0
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40552528
@Ray, I think we're on the same page on this one.

It the query may complete in 20 seconds, but the significant comparison from SSMS is to export it to a flat file on the workstation or network share.

If the bottleneck is simply shifting the bytes, the only thing (other than a faster network) is to look hard at whether all of those bytes are truly necessary: trim the number of rows or columns in the result set if possible or schedule the export  or split it so that you only move the volatile data. e.g. if it's a daily update, can you get away with joining live data for the last day with previously downloaded data for the other 29 days of the month?
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question