Excel-VBA to access sql server tables for selecting and inserting is too slow

Question

I have written an Excel-VBA code to access a SQL Server database (which is in the network, not my computer), which has a table with 10 columns and around 3000 records (basically mortgages).

The code loops through these records, and for each one : does some basic calculations to produce around 180 records (an amortization table for each mortgage), and then inserts them in a new table (Around 540000 records in total).

It goes like this (I used comments for readability and avoiding a big code here):

    Sub amortizationScheduleToSQLServer()

        'Some calculation variables go here

        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim rs2 As ADODB.Recordset

        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        Set rs2 = New ADODB.Recordset

        Server_Name = "..."
        Database_Name = "..."
        cn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server_Name & ";INITIAL CATALOG=" & Database_Name & ";Integrated Security=SSPI;"
        SQLStr = "SELECT * FROM TABLE_PRETS"
        Set rs = cn.Execute(SQLStr)

        If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst
            Do Until rs.EOF = True

                'Assigning data from table to variables here

                'Basic multiplications here, nothing heavy

                For i = 1 To paymentsNumber

                    'More basic calculations and assigning here

                    SQLStr = "INSERT INTO TABLE_AMORTISSEMENT (N_CONTRAT,MOIS,DATE_ECHEANCE,MENSUALITE,SOLDE_DEPART,CAPITAL_AMORTI," _
                        & "INTERET_HT,TVA,ASSURANCE,CAPITAL_RESTANT)" _
                        & "VALUES (" & loanID & ", " & i & ", '" & DateAdd("m", i, startDate) & "', " & Replace(monthlyPayment, ",", ".") & ", " _
                        & Replace(startingBalance, ",", ".") & ", " & Replace(principal, ",", ".") & ", " & Replace(interestPaymentBT, ",", ".") _
                        & ", " & Replace(taxOnInterest, ",", ".") & ", " & Replace(insurancePayment, ",", ".") & ", " _
                        & Replace(remainingBalance, ",", ".") & ");"
                    Set rs2 = cn.Execute(SQLStr)
                Next i

                rs.MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If

        MsgBox "Finished looping through records. "

        If rs.State = 1 Then
            rs.Close
            Set rs = Nothing
        End If

        If rs2.State = 1 Then
            rs2.Close
            Set rs2 = Nothing
        End If

        If cn.State = 1 Then
            cn.Close
            Set cn = Nothing
        End If

    End Sub

I timed this code after omitting the INSERT by deleting this line :

Set rs2 = cn.Execute(SQLStr)

The code basically loops through 3000 records and makes unused Strings, it runs for around 9 seconds. (I don't know if this is acceptable for Excel-VBA/SQL Server).

But putting back the INSERT execution line makes the code run for +1 hour to make the 540000 records table. Is this normal ? How can I optimize this code ?

Answer 1

I'm speaking a bit out of my comfort range here, but what I see is that the cn.execute statement creates a recordset that you never actually use (rs2). According to the MSDN reference on the ADO Execute Method, you can use an option (adExecuteNoRecords ) to stop the creation of the recordset when you do the insert. Maybe that would speed it up a bit?