Solved

Inserting records from Excel to SQL server slows down terribly on Windows 7

Posted on 2010-11-20
4
622 Views
Last Modified: 2012-05-10
Hi:

I am troubleshooting a VBA script running on 64 bit computer.  The Maco runs in Excel on the same box as the SQL server it is inserting into in both cases.  Both computers in this example have 8 cores and 48 GB of memory.

On Windows XP 64 bit, it inserts 10,000 records in about 5 seconds.  
On Windows 7 64 Bit, it inserts the same 10,000 records in 42 seconds.
Originally, both computers has Excel 2003 and SQL Server 2005.  in an effort to solve the slowdown on the Windows 7 box, we upgraded to Excel 2010 and SQL Server 2008 R2.  It made no measurabe difference.  Regardless of the Excel or the SQL version, the Windows 7 box inserts the records almost 10 times slower in this case. (It seems to run great in every other respect.)  The references in VBA are the same (ADO 2.8 etc.) with the exception of the Excel add in which is 11 on one and 14 on the other.  Again, the version of Excel seems to make no difference.  I put msgboxes after the steps.  All of it is almost instant on both boxes.  It's the loop toward the end after "Getting ready to insert" that takes significantly longer on Windows 7.  

 The code looks like this:

Private Sub CommandButton1_Click()
 
    Dim cnSQL As ADODB.Connection
    Dim rsXLS As ADODB.Recordset
    Dim fromSQL As String
    Dim toSQL As String
    Dim Index As Integer
    Dim XLSDrive As String
    Dim comADO As ADODB.Command
    Dim rsDB As ADODB.Recordset
    Dim strSQL As String
    Dim strConn As String
   
   

    XLSDrive = "C:\Test\D_Input_Daily.xls"
   
  Set cnSQL = New ADODB.Connection
  strConn = "Driver={SQL Server};Server=(local);Database=TestDB;Trusted_Connection=yes;"
  cnSQL.Open strConn

MsgBox "Opened SQL"


   
   Set cnXLS = New ADODB.Connection
    cnXLS.Provider = "Microsoft Jet 4.0 OLE DB Provider"
    cnXLS.ConnectionString = "Data Source = " & XLSDrive & "; Extended Properties=Excel 8.0"
    cnXLS.Open

MsgBox "Opened Excel"

       
     fromSQL = "SELECT [Date], [R1D], [PVOL], [P], [MSHS], [PSHS] FROM [Data$C1:H10000]"
     
    Set rsXLS = New ADODB.Recordset
    rsXLS.Open fromSQL, cnXLS, adOpenStatic, adLockReadOnly, adCmdText
   
   
   toSQL = "SELECT top 0 * FROM RTable"
   
   Set rsDB = New ADODB.Recordset
   rsDB.Open toSQL, cnSQL, adOpenDynamic, adLockBatchOptimistic, adCmdText
   
MsgBox "Selected Excel Records.  Getting ready to insert"
   
  If Not rsXLS.EOF Then
    Do While Not rsXLS.EOF
        rsDB.AddNew
        For Index = 0 To rsXLS.Fields.Count - 1
            rsDB.Fields(rsXLS.Fields(Index).Name).Value = rsXLS.Fields(Index).Value
        Next Index
        rsDB.Fields(1).Value = sCUSIP
        rsXLS.MoveNext
        rsDB.UpdateBatch
    Loop
End If
   MsgBox "Finished"
End Sub
0
Comment
Question by:mikerich61
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 34181075
While your attempt to speed the process up by using Batch Optimistic is noble, it is somewhat defeated by the fact that the batch is only one row. :)  A better approach would be to load the entire recordset using GetRows, rather than using MoveNext to retrieve every row.

But, have you condidered that it may be time to start using a faster approach then the old ADO "one-row-at-a-time" method:
Perhaps using  linked server and importing the data in directly with a single INSERT statement.  Of course, since there is (to my knowledge) no 64-bit JET driver you will have to drop down to 32-bit mode.  
Another solution you should consider is using SSIS.  
Finally, you can use .NET to load the whole spreadsheet in a table and then pass that table to do a single INSERT statement.

Anything better than what you are currently doing.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 34183067
Can't you just use OPENROWSET or BULK INSERT?

This is some sample code, but it doesn't deal with anywhere near as many records as you are and the queries are pretty simple.

Here's another example which use the ACE 12.0 driver.


INSERT INTO StaffLevel.dbo.XLImport178
([Account], [Field1])

SELECT [Account], [Field1]

FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\MockData\AccountNos.xls;Extended Properties=Excel 12.0')...[tblAccounts$]

That's copied from SSMS - I keep on messing up the concatentation in VBA to get the right syntax.

By the way I these might be linked to linked servers - some of the errors you get when testing certainly seem to day so.:)
      

Sub ADOXLtoSQLSRV()



Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

    Set cn = New ADODB.Connection

    cn.Open "Provider=SQLOLEDB;Data Source=NORIE-TOSH;" & _

            "Initial Catalog=StaffLevel;Trusted_Connection=yes"



    'Import by using OPENROWSET and SELECT query.

    strSQL = "INSERT INTO XLImport1 ([Account], [Field2]) SELECT [Account], [Field2] FROM " & _

             "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _

             "'Excel 8.0;Database=C:\MockData\AccountNos.xls', " & _

             "'SELECT * FROM [tblAccounts$]')"



    Debug.Print strSQL

    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

    Debug.Print "Records affected: " & lngRecsAff

Open in new window

0
 

Author Closing Comment

by:mikerich61
ID: 34300996
Thanks for the advice.  There is a bunch of code that exists so I was hoping that the solution would answer the question why SQL on the Windows 7 machine takes 10 times longer.  We will begin working on the code per the suggestions!
0
 
LVL 33

Expert Comment

by:Norie
ID: 34301367
I've used SQL Server on Windows Vista and 7, maybe even on Win98.

Never noticed any problems with speed on 7.

Perhaps it's more a network issue than anything?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now