www.softwaredevelopmentdeveloper.com
옥션 아이폰/아이팟 집에서 즐기기 롯데마트 11번가 주민등록번호 도용사실이 있는지 확인해 보세요! CJmall 회원가입 도메인 NO.1-호스팅 NO.1 든든한 웹파트너 가비아 ! 건담 전문몰 건담샵 No.1 중고차 쇼핑몰! SK엔카
아래출처는 http://ad.web2r.net/r.php?c=shop 입니다.아래의 테이블의 내용을 알수 없지만 하나의 테이블을 여러가지(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 = " & dblAvgEnd Function
