0

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 ?

Community
  • 1
  • 1
Naucle
  • 626
  • 12
  • 28
  • 1
    If it takes the program 1 hour to run, it means it produces 150 new records per second. Considering these are expensive operations, it's not surprising that the program takes a long time to finish. I doubt there's much you can do about it, except perhaps only to query fewer records, rather than all records, in the first `SQLStr` statement. That is, if it's desirable to do so. – Miqi180 Aug 24 '16 at 16:15
  • Thank you for you answer. Actually, I will be moving this code to VB.NET later on, and I was wondering if it will run faster. – Naucle Aug 24 '16 at 16:18
  • 1
    You can try adding the records in one giant statement, not sure if it'll be faster though. IE: `INSERT INTO MyTable (Name, ID) VALUES ('First',1), ('Second',2), ('Third',3), ('Fourth',4)` – Michael Russo Aug 24 '16 at 16:30
  • 2
    If I found out my mortgage provider wasn't [using parameterized queries](http://stackoverflow.com/documentation/vba/3578/working-with-ado/12354/creating-parametrized-commands#t=201608241631374408866), I'd refinance with another vendor. – Comintern Aug 24 '16 at 16:32
  • 1
    I have replaced the 540k INSERTs with only 3k INSERT by concatenating the for loop string (One single INSERT for every for loop), and it's already running faster (13 minutes), I wonder if I can go further. – Naucle Aug 24 '16 at 16:38
  • If only we can see those calculations. If they involve basic operations (Division, Multiplication, Add, Subtract), you can have SQL Server do it all without looping! Unfortunately, you leave those formulas *startingBalance*, *principal*, *interestPaymentBT*, etc. out. – Parfait Aug 24 '16 at 17:40
  • Another thought is perhaps wrapping all the Inserts into a transaction. That way you are writing the data all at one time. More info: https://msdn.microsoft.com/en-us/library/aa227162(v=vs.60).aspx – Ryan Wildry Aug 24 '16 at 17:48
  • To sum up : parameterized queries is slow for 500K queries, so is using addNew intead of INSERTs or transactions. Concatenating every 100 or 200 records into one INSERT was the fastest I could achieve. – Naucle Aug 25 '16 at 16:41
  • @Parfait can you check this other question please ? http://stackoverflow.com/questions/39161564/sql-query-to-generate-multiple-records-from-every-record-of-a-table – Naucle Aug 26 '16 at 08:20

1 Answers1

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?

Hrothgar
  • 412
  • 2
  • 5