Increase Performance

[aadebayo]

I have the code below, that writes data into a table. It takes 37 seconds to create 1000 records. Please can anyone suggest how I can modify the code to improve the time it takes to create those records? Many thanks

Private Sub cmdStartDateTest_Click()
    
    Dim start As String
    Dim myDate As Date
    Dim finish As String
        
    Dim loopCount As Integer
    txtDate = ""
    
    MsgBox "Starting Audit loop..."
    
    loopCount = 1000
    
    Do While (loopCount > 0)
    
        start = Time
        myDate = Date
        finish = Time
        
        'txtDate.SetFocus
        txtDate = myDate
      '  MsgBox "within Audit loop..."
        insertRecord start, finish
        loopCount = loopCount - 1
        
    Loop
    
    MsgBox "Audit loop finished!"
    
End Sub


Private Sub insertRecord(start As String, finish As String)
   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Set cnn = CurrentProject.Connection
   rst.Open "tblAuditTrail", cnn, adOpenDynamic, adLockPessimistic
    
    
    'rst.OpenRecordset
    
    rst.AddNew
    rst!dateUpdate = start
    rst!Record = "Amjad,Ali Movement 16/02/2011 11:49:05  HistoryDate=16/02/2011 HistoryTime=11:49 HistoryNotes=Test test LocationID=4 Dunstable Road CrimeID=Adbuction"
    rst.Update
    
    rst.Close
    Set rst = Nothing
End Sub

 

[spikepl]

Don't open connection for each record - open once, write records, then close

 

[Galaxiom]

Spike's advice should do the trick.

However I have also heard that disconnecting the recordset while processing allows it to be updated without continually checking for changes by other users.

If you are connecting on a network, the disconnected recordset has the entire batch of writing done locally rather than repeated network traffic back to the table. Then the recordset is reconnected and it updates the table.

If you have enough RAM the recordset only exists in RAM, avoiding disk access entirely unitil it reconnects.

I have never done this myself so am no expert but it might be worth researching if you have huge volumes of data to process and speed really matters.

 

[aadebayo]

Thanks Spikepl, you suggestion reduced the time to between 2-3 second.