Solved

MS SQL to VBA-Excel large data dump

Posted on 2015-01-14
6
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 11

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 11

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

630 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