Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleCompare different methods for using DAO to find records
DescriptionThis example shows how to compare different methods for using DAO to find records in Visual Basic 6.
Keywordsdatabase, DAO, index, key, Seek, FindFirst
CategoriesDatabase
 
In many applications, simple changes to the database and how you use it can give big performance improvements. This example shows the difference between using FindFirst and Seek to locate values.

The program searches for values in four ways:

Open a dynaset and use FindFirst on the table's primary key field

 
' Find the values using FindFirst on the primary key in a
' dynaset.
Private Sub cmdFindFirstPrimary_Click()
Dim start_time As Single
Dim stop_time As Single

Dim i As Long
Dim rs As DAO.Recordset

    lblFindFirstPrimary.Caption = ""
    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create a recordset.
    Set rs = m_DB.OpenRecordset( _
        "SELECT * FROM IndexedTable", _
        Type:=dbOpenDynaset)

    DoEvents
    start_time = Timer
    For i = 1 To m_NumValues
        rs.FindFirst "PrimaryKeyValue=" & i
        If i Mod 10 = 0 Then Caption = i
    Next i
    stop_time = Timer

    rs.Close

    Caption = App.ProductName
    lblFindFirstPrimary.Caption = Format$(stop_time - _
        start_time, "0.00") & " sec"
    Screen.MousePointer = vbDefault
End Sub
 

Open a dynaset and use FindFirst on a non-indexed field

 
' Find the values using FindFirst on the primary key in a
' dynaset.
Private Sub cmdFindFirstNonIndexed_Click()
Dim start_time As Single
Dim stop_time As Single

Dim i As Long
Dim rs As DAO.Recordset

    lblFindFirstNonIndexed.Caption = ""
    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create a recordset.
    Set rs = m_DB.OpenRecordset( _
        "SELECT * FROM IndexedTable", _
        Type:=dbOpenDynaset)

    DoEvents
    start_time = Timer
    For i = 1 To m_NumValues
        rs.FindFirst "NonIndexedValue=" & i
        If i Mod 10 = 0 Then Caption = i
    Next i
    stop_time = Timer

    rs.Close

    Caption = App.ProductName
    lblFindFirstNonIndexed.Caption = Format$(stop_time - _
        start_time, "0.00") & " sec"
    Screen.MousePointer = vbDefault
End Sub
 

Open a table recordset and use Seek on the table's primary index

 
' Find the values using Seek on the primary key in a table
' recordset.
Private Sub cmdSeekPrimary_Click()
Dim start_time As Single
Dim stop_time As Single

Dim i As Long
Dim rs As DAO.Recordset

    lblSeekPrimary.Caption = ""
    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create a recordset.
    Set rs = m_DB.OpenRecordset( _
        "IndexedTable", _
        Type:=dbOpenTable)
    rs.Index = "PrimaryKey"

    DoEvents
    start_time = Timer
    For i = 1 To m_NumValues
        rs.Seek "=", i
        If i Mod 10 = 0 Then
            Caption = i
            DoEvents
        End If
    Next i
    stop_time = Timer

    rs.Close

    Caption = App.ProductName
    lblSeekPrimary.Caption = Format$(stop_time - _
        start_time, "0.00") & " sec"
    Screen.MousePointer = vbDefault
End Sub
 

Open a table recordset and use Seek on a secondary index

 
' Find the values using Seek on the secondary key in a
' table recordset.
Private Sub cmdSeekSecondary_Click()
Dim start_time As Single
Dim stop_time As Single

Dim i As Long
Dim rs As DAO.Recordset

    lblSeekSecondary.Caption = ""
    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create a recordset.
    Set rs = m_DB.OpenRecordset( _
        "IndexedTable", _
        Type:=dbOpenTable)
    rs.Index = "SecondaryValue"

    DoEvents
    start_time = Timer
    For i = 1 To m_NumValues
        rs.Seek "=", i
        If i Mod 10 = 0 Then
            Caption = i
            DoEvents
        End If
    Next i
    stop_time = Timer

    rs.Close

    Caption = App.ProductName
    lblSeekSecondary.Caption = Format$(stop_time - _
        start_time, "0.00") & " sec"
    Screen.MousePointer = vbDefault
End Sub
 
In one test, I got these results:

MethodTime (sec)
DynaSet/FindFirst/Primary Index0.88
DynaSet/FindFirst/Non-Indexed Field3.13
Table/Seek/Primary Index0.11
Table/Seek/Secondary Index0.11

The moral is, use an index whenever possible. Use a table recordset and Seek if possible. Use FindFirst only if you need to search on fields that are not indexed.

 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated