O、我所知道至少有2种access产生随机记录的办法
方法1、采用sql查询随机排序,形如
objCon.execute("select * from tblTable order by rnd(-" & rnd&")"
方法2、先读取全表,然后采用absoluteposition随机定位
objRst.open "tblTable",objCon,3,3,2
for i=1 to N
objRst.absolutePosition=fix(rnd*objRst.recordCount)+1 'abpos以1开头
objRst("xxxx")......
next
O、这两种方法各有优劣
方法1、需要先由sql对全表排序,初始话时间较长,但是一次读取即可,通过设置cachsize,可能全部缓存
方法2、无需对复杂查询排序,初始化时间短,但读取全表后再处理,由于不可能把全表全部缓存,每次设置absoluteposition都要重新读数据库。
O、有限测试
我对这两种方法进行了简单的测试,测试目标为一个结构为5000条记录的TABLE[id,strValue]的简单数据库,反复1000次每次随机生成1000条记录,经测试,在adCmdTable下产生随机读取数据,方法1在速度上大约较2快1倍以上。测试log如下。
---------------Rnd by sql----------------
2008-8-30 11:44:54/0
2008-8-30 11:44:57/50
2008-8-30 11:44:59/100
2008-8-30 11:45:02/150
2008-8-30 11:45:04/200
2008-8-30 11:45:07/250
2008-8-30 11:45:09/300
2008-8-30 11:45:12/350
2008-8-30 11:45:14/400
2008-8-30 11:45:17/450
2008-8-30 11:45:19/500
2008-8-30 11:45:21/550
2008-8-30 11:45:24/600
2008-8-30 11:45:26/650
2008-8-30 11:45:29/700
2008-8-30 11:45:31/750
2008-8-30 11:45:34/800
2008-8-30 11:45:36/850
2008-8-30 11:45:39/900
2008-8-30 11:45:41/950
2008-8-30 11:45:44/1000
---------------Rnd by Position----------------
2008-8-30 11:45:44/0
2008-8-30 11:45:49/50
2008-8-30 11:45:54/100
2008-8-30 11:45:59/150
2008-8-30 11:46:04/200
2008-8-30 11:46:10/250
2008-8-30 11:46:15/300
2008-8-30 11:46:20/350
2008-8-30 11:46:25/400
2008-8-30 11:46:30/450
2008-8-30 11:46:36/500
2008-8-30 11:46:41/550
2008-8-30 11:46:46/600
2008-8-30 11:46:51/650
2008-8-30 11:46:56/700
2008-8-30 11:47:01/750
2008-8-30 11:47:07/800
2008-8-30 11:47:12/850
2008-8-30 11:47:17/900
2008-8-30 11:47:22/950
2008-8-30 11:47:27/1000
O、结论
对于结构简单的查询,采用sql随机较好
附测试代码:
- Option Explicit
- Const DB_NAME="test.mdb" '数据库名
- Const DB_RECORD_SIZE=5000 '数据库中记录数
- Const TEST_SIZE=1000 '从重复测试的次数
- Const TEST_RECORD_SIZE=1000 '从数据库中随机读取的记录数
- '创建数据库,仅在第一次时调用
- Sub createDb()
- Dim objAdc,objCon,objFo
- Dim strCon
- Set objFo=CreateObject("scripting.filesystemobject")
- If objFo.fileExists(DB_NAME) Then objFo.deleteFile(DB_NAME)
- strCon="provider=microsoft.jet.oledb.4.0; data source=" & DB_NAME
- Set objAdc=CreateObject("adox.catalog")
- Set objCon=objAdc.create(strCon)
- objCon.Execute "CREATE TABLE tblTest"&_
- "(lngID COUNTER(1,1) PRIMARY KEY,"&_
- "strValue TEXT(20) )"
- Dim i_
- For i_=0 To DB_RECORD_SIZE-1
- objCon.execute "INSERT INTO tblTEST (strValue) VALUES ('" & "[" & i_ & "]" & "')"
- Next
- If IsObject(objCon) Then
- If objCon.State<>0 Then objCon.close
- Set objCon=Nothing
- End If
- Set objAdc=Nothing
- Set objFo=Nothing
- MsgBox "Db created."
- End Sub
- '添加记录到文件
- Sub appendLog(strName,strData)
- Dim objTs
- Set objTs=CreateObject("scripting.filesystemobject").openTextFile(strName,8,True)
- objTs.writeLine strData
- objTs.close
- End Sub
- '=========================
- '首先测试选取10000
- Sub main()
- Dim objCon,objRst
- Dim i,j,k,a,b
- Dim strSql
- Randomize
- Set objCon=CreateObject("adodb.connection")
- objCon.open "provider=microsoft.jet.oledb.4.0; data source=" & DB_NAME
- 'test1
- appendLog "log.txt","---------------Rnd by sql----------------"
- For i=0 To TEST_SIZE
- Set objRst=objCon.execute("select top " & TEST_RECORD_SIZE & " * from tblTest order by rnd(" & Rnd & "-lngId)")
- Do While Not( objRst.bof Or objRst.eof)
- a=objRst("strValue")
- objRst.moveNext
- Loop
- objRst.close
- '每隔50次测试记录一次时间
- If i Mod 50 =0 Then appendLog "log.txt",Now() & "/" & i
- Next
- Set objRst=Nothing
- 'test 2
- appendLog "log.txt","---------------Rnd by Position----------------"
- Set objRst=CreateObject("adodb.recordset")
- For i=0 To TEST_SIZE
- objRst.open "tblTest",objCon,3,3,2
- For j=0 To TEST_RECORD_SIZE-1
- k=Fix(objRst.RecordCount*rnd)+1
- objRst.AbsolutePosition=k
- a=objRst("strValue")
- Next
- objRst.close
- '每隔50次测试记录一次时间
- If i Mod 50 =0 Then appendLog "log.txt",Now() & "/" & i
- Next
- objCon.close
- MsgBox "main end"
- End Sub
- main