How long it will take to process a query with >100,000 record

[Petercheng]

Hi Guys,

Do you have any idea as to how long it will take to process a query in a database with 100,000 records? Any relationship between the processing time and database size?

Thanks,

Peter

 

[MarkK]

There are too many factors that affect query performance to answer this question in a meaningful way, but 100,000 records is not that many. So I just tested this code and added 100000 records in 1.21875 seconds...

Sub test092q834709()
    Dim rst As DAO.Recordset
    Dim i As Long
    Dim clock As Single
    
    clock = Timer
    DBEngine.Workspaces(0).BeginTrans
    Set rst = CurrentDb.OpenRecordset("tTable")
    With rst
        For i = 1 To 100000
            .AddNew
            !Data = i
            .Update
        Next
        .Close
    End With
    DBEngine.Workspaces(0).CommitTrans
    Debug.Print "Added 100000 records in " & Timer - clock & " seconds"
    Beep
End Sub

...and that's using a recordset which is about the slowest way to go about it.
This code updates those 100000 records in 0.75 seconds...

Sub Test802367490()
    Dim clock As Single
    
    clock = Timer
    CurrentDb.CreateQueryDef("", "UPDATE tTable SET Data = Data + 1").Execute
    Debug.Print "Updated 100000 records in " & Timer - clock & " seconds"
    Beep
    
End Sub

Cheers,