Solved Select Query on 10k or Seek on Millions Faster? (2 Viewers)

D

dalski

Member
Local time
Today, 00:02
Joined
Jan 5, 2025
Messages
171
Faster solution on a single-user system where records increase by around 20k per week. Will be going to a multi-user system where that could be around 100k per week (but that's an idea atm):
  1. Select Query on 10k records based on a parent query to restrict the amount of items or
  2. Seek on Millions of records in a table?
My guess would be (1).
 
Last edited:
Not really sure what you're asking!

Are you looking to select a single record from the 10000?

As long as you have a index on whatever is used as the criteria then select will be as fast as Seek.

Seek is used to find a record within an already opened recordset, which could take a long time to open in the first place.

SELECT will open a recorded with just the desired record
 
Thanks Dave, yes just a single record & thank you for warning me that opening a hefty recordset delays things. SELECT is the way to go here I think as it will be called from many different places where i don't know what will be happening at runtime.
 
If you are searching only one record and the table is indexed on that field, the method seek in a table recordset is faster.
The table must be in an MS Access Database, Seek doesn't work on SQL Server.
 
Last edited:
IIRC, seek doesn’t work with a linked table unless you use the remote DB rather than currentdb.

You could also test and report back 😉
 
At 1st I thought the select would be faster so I did a test on a large table with 300000 + records and seek was faster in this test.

TestSelectVSeek Time in seconds
Select time: 18
Select Parm time: 15
Seek time: 14

Code: 
Public Sub TestSelectVSeek()
    On Error Resume Next
    Dim PartID(1000) As String
    Dim i As Long
    Dim TimerTest As Date
    
    With CurrentDb.OpenRecordset("SELECT tblParts.[PART#] " & _
                                 "FROM tblParts " & _
                                 "WHERE (((tblParts.Description) Like ""*Rail*""));")
        .MoveFirst
        For i = 0 To 1000
            PartID(i) = ![Part#]
            .MoveNext
        Next
    .Close
    End With

    TimerTest = Now
    For i = 0 To 1000
        With CurrentDb.OpenRecordset( _
                "SELECT tblParts.* " & _
                "FROM tblParts " & _
                "WHERE (((tblParts.[PART#])=""" & _
                PartID(i) & """));")
        .Close
        End With
    Next
    Debug.Print "Select time: " & DateDiff("s", TimerTest, Now)
    
    TimerTest = Now
    With CurrentDb.CreateQueryDef( _
                vbNullString, _
                "PARAMETERS SearchPart Text ( 255 );" & _
                "SELECT tblParts.* " & _
                "FROM tblParts " & _
                "WHERE (((tblParts.[PART#])=[SearchPart]));")
        For i = 0 To 1000
            .Parameters(0) = PartID(i)
            With .OpenRecordset()
            .Close
            End With
        Next
    End With
    Debug.Print "Select Parm time: " & DateDiff("s", TimerTest, Now)
    
    TimerTest = Now
    For i = 0 To 1000
        With CurrentDb.OpenRecordset("tblParts")
            .Index = "PrimaryKey"
            .Seek "=", PartID(i)
        .Close
        End With
    Next
    Debug.Print "Seek time: " & DateDiff("s", TimerTest, Now)
End Sub
 
RonPaii said:
At 1st I thought the select would be faster so I did a test on a large table with 300000 + records and seek was faster in this test.

TestSelectVSeek Time in seconds
Select time: 18
Select Parm time: 15
Seek time: 14

Code: 
Public Sub TestSelectVSeek()
    On Error Resume Next
    Dim PartID(1000) As String
    Dim i As Long
    Dim TimerTest As Date
  
    With CurrentDb.OpenRecordset("SELECT tblParts.[PART#] " & _
                                 "FROM tblParts " & _
                                 "WHERE (((tblParts.Description) Like ""*Rail*""));")
        .MoveFirst
        For i = 0 To 1000
            PartID(i) = ![Part#]
            .MoveNext
        Next
    .Close
    End With

    TimerTest = Now
    For i = 0 To 1000
        With CurrentDb.OpenRecordset( _
                "SELECT tblParts.* " & _
                "FROM tblParts " & _
                "WHERE (((tblParts.[PART#])=""" & _
                PartID(i) & """));")
        .Close
        End With
    Next
    Debug.Print "Select time: " & DateDiff("s", TimerTest, Now)
  
    TimerTest = Now
    With CurrentDb.CreateQueryDef( _
                vbNullString, _
                "PARAMETERS SearchPart Text ( 255 );" & _
                "SELECT tblParts.* " & _
                "FROM tblParts " & _
                "WHERE (((tblParts.[PART#])=[SearchPart]));")
        For i = 0 To 1000
            .Parameters(0) = PartID(i)
            With .OpenRecordset()
            .Close
            End With
        Next
    End With
    Debug.Print "Select Parm time: " & DateDiff("s", TimerTest, Now)
  
    TimerTest = Now
    For i = 0 To 1000
        With CurrentDb.OpenRecordset("tblParts")
            .Index = "PrimaryKey"
            .Seek "=", PartID(i)
        .Close
        End With
    Next
    Debug.Print "Seek time: " & DateDiff("s", TimerTest, Now)
End Sub
Click to expand...
 
Add 2 more test where the recordset is not recreated in the loop, required 10,000 searches to see a difference.
Note: it the previous test I neglected to user dbOpenTable on the seek recordset, which is requited.

Select time: 15 Seconds, Found: 1000
Select Parm time: 13 Seconds, Found: 1000
Select Parm requry time: 0 Seconds, Found: 1000
Seek time: 1 Seconds, Found: 1000
Seek multi time: 0 Seconds, Found: 1000

Select Parm requry time: 2 Seconds, Found: 10000
Seek multi time: 0 Seconds, Found: 10000

Code: 
    Dim qryD As DAO.QueryDef
    Dim rs As DAO.Recordset2
    TimerTest = Now
    Found = 0
    Set qryD = CurrentDb.CreateQueryDef( _
                vbNullString, _
                "PARAMETERS SearchPart Text ( 255 );" & _
                "SELECT tblParts.* " & _
                "FROM tblParts " & _
                "WHERE (((tblParts.[PART#])=[SearchPart]));")
    With qryD
        .Parameters(0) = PartID(0)
        Set rs = .OpenRecordset()
        For i = 1 To UBound(PartID)
            If rs.RecordCount > 0 Then
                rs.MoveFirst
                If rs![Part#] = PartID(i - 1) Then
                    Found = Found + 1
                End If
            End If
            .Parameters(0) = PartID(i)
            rs.Requery qryD
        Next
        .Close
        Set rs = Nothing
    End With
    Set qryD = Nothing
    Debug.Print "Select Parm requry time: " & _
                DateDiff("s", TimerTest, Now) & _
                " Seconds, Found: " & _
                Found
                
    TimerTest = Now
    Found = 0
    With CurrentDb.OpenRecordset("tblParts", dbOpenTable)
        .Index = "PrimaryKey"
        For i = 0 To UBound(PartID)
            .Seek "=", """" & PartID(i) & """"
            If Not .NoMatch Then
                If ![Part#] = PartID(i) Then
                    Found = Found + 1
                End If
            End If
        Next
    .Close
    End With
    Debug.Print "Seek multi time: " & _
                DateDiff("s", TimerTest, Now) & _
                " Seconds, Found: " & _
                Found - 1
 
Never use a recordset to navigate records if you can use criteria in a query when your recordset is large.
 
You must log in or register to reply here.

Similar threads

S
Solved Getting "No Current Record" on query that previously worked
Replies
6
Views
729
Gasman
Gasman
N
Query to show records EXCEPT those with matching fields
Replies
8
Views
348
nortonm
N
P
Long-running delete of duplicates
2
Replies
25
Views
2,382
CJ_London
C
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
900
SkeletorPlus
S
C
Solved No Current Record on Access Close
Replies
11
Views
1,242
Pat Hartman
P

Users who are viewing this thread

Total: 3 (members: 0, guests: 3)
Back
Top Bottom