下面这个贴子中讨论了很多access中的一些猜想假设。
access或其它数据库操作效率问题.欢迎讨论
引用 28 楼 wwwwb 的回复::
比如插入10万条记录，速度 <插入1万条记录；
面对这个问题，第一想法上，在一个mdb文件插入过多的记录，自然会导致MDB文件增大，需要不停地向操作系统申请磁盘空间的分配。因故会导致速度慢。但再仔细一想好象也没什么道理，毕竟加10000条也要申请10000条记录的空间，速度上应该没什么影响。毕竟access中又不会去用什么redo / undo log.
于是便做了个简单的测试如下以对这个猜想证实。
新建空的 t.mdb 文件，创建表 table1 (id int primary key,cname varchar(10)
然后新建 模块，内容如下。
view plaincopy to clipboardprint?
Option Compare Database
Option Explicit
Public Sub t1(nRowCnt As Long)
Dim i As Long
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
For i = 1 To nRowCnt
conn.Execute "insert into table1(id,cname) values(" & i & ",'" & i & "')"
Next i
End Sub
Public Sub t()
CurrentProject.Connection.Execute "delete from table1"
Debug.Print "t10000 start.", Now
Call t1(10000)
Debug.Print "t10000 end .", Now
CurrentProject.Connection.Execute "delete from table1"
Debug.Print "t100000 start.", Now
Call t1(100000)
Debug.Print "t100000 end .", Now
End Sub
Option Compare Database
Option Explicit
Public Sub t1(nRowCnt As Long)
Dim i As Long
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
For i = 1 To nRowCnt
conn.Execute "insert into table1(id,cname) values(" & i & ",'" & i & "')"
Next i
End Sub
Public Sub t()
CurrentProject.Connection.Execute "delete from table1"
Debug.Print "t10000 start.", Now
Call t1(10000)
Debug.Print "t10000 end .", Now
CurrentProject.Connection.Execute "delete from table1"
Debug.Print "t100000 start.", Now
Call t1(100000)
Debug.Print "t100000 end .", Now
End Sub
运行 t() 结果如下：
t10000 start. 5/14/2009 7:53:10 PM
t10000 end . 5/14/2009 7:53:29 PM
t100000 start. 5/14/2009 7:53:29 PM
t100000 end . 5/14/2009 7:56:06 PM
t10000 . 19s
t100000 . 157 s
试验结论：
插入10万条的总时间显然比插入1万长(157s>19s)，但速度显然快(157/100000<19/10000)
看来实践是检验的唯一标准啊。
