mikerich61
asked on
Inserting records from Excel to SQL server slows down terribly on Windows 7
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);Dat abase=Test DB;Trusted _Connectio n=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(I ndex).Name ).Value = rsXLS.Fields(Index).Value
Next Index
rsDB.Fields(1).Value = sCUSIP
rsXLS.MoveNext
rsDB.UpdateBatch
Loop
End If
MsgBox "Finished"
End Sub
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);Dat
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(I
Next Index
rsDB.Fields(1).Value = sCUSIP
rsXLS.MoveNext
rsDB.UpdateBatch
Loop
End If
MsgBox "Finished"
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Never noticed any problems with speed on 7.
Perhaps it's more a network issue than anything?
ASKER