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

[dalski]

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).

 

[cheekybuddha]

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

 

[dalski]

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.

 

[xavier.batlle]

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.

 

[DHookom]

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

 

[RonPaii]

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

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]

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

Seek works with a linked Access table.
tblParts in my example is linked.

 

[cheekybuddha]

"WHERE (((tblParts.Description) Like ""*Rail*""));")

Even if [Description] is indexed, the index can't be used with this criteria

 

[RonPaii]

Even if [Description] is indexed, the index can't be used with this criteria

That 1st query is used to fill the array with part numbers to search and is not included in the timer.

 

[RonPaii]

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

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]

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

    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

 

[Pat Hartman]

Never use a recordset to navigate records if you can use criteria in a query when your recordset is large.