아래의 테이블의 내용을 알수 없지만 하나의 테이블을 여러가지(sql, seek, findfirst)방법으로 자료를 검색하는 속도를 테스트 한 것입니다. 여기서는 seek방법이 가장 빠른 것으로 나타납니다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
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

Copyright By AccessVision