Current thinking on using SEEK...speed

Question

I was under the impression that - on a large set of data - Seek was "much" faster. Does anyone have a clue about parameters (How much is "much faster" and large is "large")?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Current thinking on using SEEK...speed?
Date: 5 July 1998 23:04

Stephens,

Here are the results of the test I used on Pentium 166/64MB and MS Access 97.  I think they show that seek is an obsolete technique for MS Access 97 and FindFirst is "forbidden" if you are going to write an app with predictable anwer time for lookup queries. If you find time to run this test on your PC/database and get different results/find errors in test itself please post them here.

TableName = tblPartSaldo
RowsQty = 38183
1000 cycles.

Qdf:       STime = 1998.07.05 22:41:14, ETime = 1998.07.05 22:41:30, Avg = 0,016
SQL:       STime = 1998.07.05 22:41:30, ETime = 1998.07.05 22:41:45, Avg = 0,015
Seek:      STime = 1998.07.05 22:41:45, ETime = 1998.07.05 22:41:50, Avg = 0,005
FindFirst: STime = 1998.07.05 22:41:50, ETime = 1998.07.05 22:44:12, Avg = 0,142

TableName = tblCompany
RowsQty = 656
1000 cycles.

Qdf:       STime = 1998.07.05 22:48:29, ETime = 1998.07.05 22:48:45, Avg = 0,016
SQL:       STime = 1998.07.05 22:48:45, ETime = 1998.07.05 22:49:02, Avg = 0,017
Seek:      STime = 1998.07.05 22:49:02, ETime = 1998.07.05 22:49:11, Avg = 0,009
FindFirst: STime = 1998.07.05 22:49:11, ETime = 1998.07.05 22:49:29, Avg = 0,018

TableName = tblGlossary
RowsQty = 49
1000 cycles.

Qdf:       STime = 1998.07.05 22:52:59, ETime = 1998.07.05 22:53:15, Avg = 0,016
SQL:       STime = 1998.07.05 22:53:15, ETime = 1998.07.05 22:53:30, Avg = 0,015
Seek:      STime = 1998.07.05 22:53:30, ETime = 1998.07.05 22:53:33, Avg = 0,003
FindFirst: STime = 1998.07.05 22:53:33, ETime = 1998.07.05 22:53:48, Avg = 0,015

HTH,
Shamil

P.S. The code:

Public Function a_test()
    Dim strDBPath As String
    Dim strTblName As String
    Dim strIdFldName As String
    Dim strIdxName As String
    Dim strLkpFldName As String
    
    strDBPath = "C:\test\serverdb.mdb"
    
    'strTblName = "tblPartSaldo"
    'strIdFldName = "PartSaldoId"
    'strIdxName = "PrimaryKey"
    'strLkpFldName = "PartSaldoCred"
    
    'strTblName = "tblCompany"
    'strIdFldName = "CompId"
    'strIdxName = "PrimaryKey"
    'strLkpFldName = "CompName"
    
    strTblName = "tblGlossary"
    strIdFldName = "GlossId"
    strIdxName = "AltKey"
    strLkpFldName = "GlossName"
    
    FindTest strDBPath, strTblName, strIdFldName, strIdxName, strLkpFldName, 1000
End Function

Public Function FindTest(ByVal vstrDBPath As String, _
                       ByVal vstrTblName As String, _
                       ByVal vstrIdFldName As String, _
                       ByVal vstrIdxName As String, _
                       ByVal vstrLkpFldName As String, _
                       ByVal vlngCyclesQty As Long)
                       
    Dim dbs As Database
    Dim rst As Recordset
    Dim qdf As QueryDef
    Dim strSql As String
    Dim strSqlQdfFind As String
    Dim strSqlFind As String
    
    Dim avarId As Variant
    Dim lngRowsQty As Long
    Dim i As Integer
    Dim lngIdx As Long
    
    Dim datSTime As Date
    Dim datETime As Date
    Dim dblAvg As Double
    Dim varValue As Variant
    
    strSql = "select [" & vstrIdFldName & "] from [" & vstrTblName & "]"
    
    Set dbs = DBEngine(0).OpenDatabase(vstrDBPath)
    Set rst = dbs.OpenRecordset(strSql, dbOpenSnapshot)
    rst.MoveLast
    lngRowsQty = rst.RecordCount
    Debug.Print "TableName = " & vstrTblName
    Debug.Print "RowsQty = " & lngRowsQty
    
    rst.MoveFirst
    avarId = rst.GetRows(lngRowsQty)

    Randomize
    
    dbs.Close
    
    Set dbs = DBEngine(0).OpenDatabase(vstrDBPath)
    strSql = "select [" & vstrIdFldName & "],[" & vstrLkpFldName & "] from [" & vstrTblName & "]"

    Debug.Print vlngCyclesQty & " cycles."
    Debug.Print
       
    datSTime = Now
    Dim rstQdfSql As Recordset
    strSqlQdfFind = strSql & " where ([" & vstrIdFldName & "] = [IdFieldValue])"
    Set qdf = dbs.CreateQueryDef("", strSqlQdfFind)
    For i = 1 To vlngCyclesQty
        lngIdx = CLng((lngRowsQty - 1) * Rnd)
        qdf.Parameters("IdFieldValue") = avarId(0, lngIdx)
        Set rstQdfSql = qdf.OpenRecordset(dbOpenSnapshot)
        varValue = rstQdfSql(vstrLkpFldName)
        'Debug.Print lngIdx
    Next
    datETime = Now
    
    dblAvg = CDbl(DateDiff("s", datSTime, datETime)) / vlngCyclesQty
    Debug.Print "Qdf:       STime = " & datSTime & ", ETime = " & datETime & ", Avg = " & dblAvg
    
    datSTime = Now
    Dim rstSql As Recordset
    For i = 1 To vlngCyclesQty
        lngIdx = CLng((lngRowsQty - 1) * Rnd)
        strSqlFind = strSql & " where ([" & vstrIdFldName & "] = " & avarId(0, lngIdx) & ")"
        Set rstSql = dbs.OpenRecordset(strSqlFind, dbOpenSnapshot)
        varValue = rstSql(vstrLkpFldName)
        'Debug.Print lngIdx
    Next
    datETime = Now
    
    dblAvg = CDbl(DateDiff("s", datSTime, datETime)) / vlngCyclesQty
    Debug.Print "SQL:       STime = " & datSTime & ", ETime = " & datETime & ", Avg = " & dblAvg
    
    datSTime = Now
    Dim rstSeek As Recordset
    For i = 1 To vlngCyclesQty
        Set rstSeek = dbs.OpenRecordset(vstrTblName, dbOpenTable)
        lngIdx = CLng((lngRowsQty - 1) * Rnd)
        rstSeek.Index = vstrIdxName
        rstSeek.Seek "=", avarId(0, lngIdx)
        varValue = rstSeek(vstrLkpFldName)
        'Debug.Print lngIdx
    Next
    datETime = Now
   
    dblAvg = CDbl(DateDiff("s", datSTime, datETime)) / vlngCyclesQty
    Debug.Print "Seek:      STime = " & datSTime & ", ETime = " & datETime & ", Avg = " & dblAvg

    datSTime = Now
    Dim rstFind As Recordset
    For i = 1 To vlngCyclesQty
        Set rstFind = dbs.OpenRecordset(strSql, dbOpenDynaset)
        lngIdx = CLng((lngRowsQty - 1) * Rnd)
        rstFind.FindFirst "[" & vstrIdFldName & "] = " & avarId(0, lngIdx)
        varValue = rstFind(vstrLkpFldName)
        'Debug.Print lngIdx
    Next
    datETime = Now

    dblAvg = CDbl(DateDiff("s", datSTime, datETime)) / vlngCyclesQty
    Debug.Print "FindFirst: STime = " & datSTime & ", ETime = " & datETime & ", Avg = " & dblAvg

End Function