| Title | Compare different methods for using DAO to find records |
|---|
| Description | This example shows how to compare different methods for using DAO to find records in Visual Basic 6. |
|---|
| Keywords | database, DAO, index, key, Seek, FindFirst |
|---|
| Categories | Database |
|---|
|
|
|
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:
| Method | Time (sec) |
|---|
| DynaSet/FindFirst/Primary Index | 0.88 |
| DynaSet/FindFirst/Non-Indexed Field | 3.13 |
| Table/Seek/Primary Index | 0.11 |
| Table/Seek/Secondary Index | 0.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.
|
| |
|
| |
