下面这个贴子中讨论了很多access中的一些猜想假设。 Access或其它数据库操作效率问题.欢迎讨论 http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor 引用 9 楼 wwwwb 的回复:: 你自己可以测试一下,不过根据经验, D下面这个贴子中讨论了很多access中的一些猜想假设。
Access或其它数据库操作效率问题.欢迎讨论http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor引用 9 楼 wwwwb 的回复::你自己可以测试一下,不过根据经验,DELETE,再INSERT(数据量比只插入没有的记录大 ) ,再建索引,速度比较慢。
比如表 table1 (id int primary key,cname varchar(10)+----+-------+| id | cname |+----+-------+| 1 | 1 || 2 | 2 || 3 | 3 || 4 | 4 || 5 | 5 || 6 | 6 || 7 | 7 || 8 | 8 || .. | ... |+----+-------+如果现在有一记录 (1234, 'KKKK'), 需要添加。那么两种方法。
方法一: 先做判断记录 1234是否存在,存在的话就 update table1 set cname = 'KKKK' where id=1234; 否则就 insert into table1 values(1234,'KKKK');方法二: 不做任何判断,直接先 delete from table1 where id=1234; insert into table1 values(1234,'KKKK');那么哪一种方法快?
在未做试验前,感觉上第二种方法,删除再添加记录应该是比较慢的一种。 但只是凭感觉,事实上是什么样呢,于是做了个简单的测试。
ACCESS 2003 SP3 + Windows 2000 SP4新建一个空的t.mdb文件,然后建表 table1 (id int primary key,cname varchar(10)新建一个模块,代码如下。
Option Compare Database Option Explicit
Public Sub tx()
Dim i As Integer For i = 1 To 10000 CurrentProject.Connection.Execute "insert into table1 values(" & i & ",'" & i & "')"Next End Sub
Public Sub t1()'CurrentProject.Connection.Execute "delete from table1 where id=1234"
Dim rs As New ADODB.Recordset Dim ssql As String ssql = "select * from table1 where id=1234"rs.Open ssql, CurrentProject.Connection, adOpenStatic, adLockOptimistic If rs.EOF Then rs.AddNew rs.Fields("id").Value = 1234 End If rs.Fields("cname").Value = "KKK"rs.Update rs.Close End Sub
Public Sub t2()'CurrentProject.Connection.Execute "delete from table1 where id=1234"
Dim rs As New ADODB.Recordset Dim ssql As String ssql = "select * from table1 where id=1234"rs.Open ssql, CurrentProject.Connection If rs.EOF Then ssql = "insert into table1 values(1234,'1234')"Else ssql = "update table1 set cname='1234' where id=1234"End If rs.Close CurrentProject.Connection.Execute ssql End Sub
Public Sub t3()'CurrentProject.Connection.Execute "delete from table1 where id=1234"
Dim ssql As String Dim nAffectedRow As Long
ssql = "update table1 set cname='1234' where id=1234"CurrentProject.Connection.Execute ssql, nAffectedRow
If nAffectedRow = 0 Then ssql = "insert into table1 values(1234,'1234')"CurrentProject.Connection.Execute ssql, nAffectedRow End If
End Sub
Public Sub t4()'CurrentProject.Connection.Execute "delete from table1 where id=1234"
Dim ssql As String
ssql = "delete from table1 where id=1234"CurrentProject.Connection.Execute ssql
ssql = "insert into table1 values(1234,'1234')"CurrentProject.Connection.Execute ssql
End Sub
Public Sub t() Dim i As Integer Dim nCnt As Integer nCnt = 5000
Debug.Print "t1 start.", Now For i = 1 To nCnt Call t1 Next i Debug.Print "t1 end .", Now
Debug.Print "t2 start.", Now For i = 1 To nCnt Call t2 Next i Debug.Print "t2 end .", Now
Debug.Print "t3 start.", Now For i = 1 To nCnt Call t3 Next i Debug.Print "t3 end .", Now
Debug.Print "t4 start.", Now For i = 1 To nCnt Call t4 Next i Debug.Print "t4 end .", Now
End Sub结果很有趣。在把'CurrentProject.Connection.Execute "delete from table1 where id=1234"这一句注释的情况下,也就是表中存在该记录。需要进行更新。
PHP数据库链接类(PDO+Access) PHP数据库链接类(PDO+Access)下载表中记录已存在 测试结果:t1 start. 5/14/2009 12:02:36 PMt1 end . 5/14/2009 12:02:42 PMt2 start. 5/14/2009 12:02:42 PMt2 end . 5/14/2009 12:02:52 PMt3 start. 5/14/2009 12:02:52 PMt3 end . 5/14/2009 12:02:57 PMt4 start. 5/14/2009 12:02:57 PMt4 end . 5/14/2009 12:03:05 PM把这个注释找开,即模拟记录不存在的情况。
表中记录不存在 测试结果:t1 start. 5/14/2009 12:14:22 PMt1 end . 5/14/2009 12:14:34 PMt2 start. 5/14/2009 12:14:34 PMt2 end . 5/14/2009 12:14:48 PMt3 start. 5/14/2009 12:14:48 PMt3 end . 5/14/2009 12:15:01 PMt4 start. 5/14/2009 12:15:01 PMt4 end . 5/14/2009 12:15:14 PM在更新情况下各方法所耗时。t1: 6s t2: 10s t3: 5s t4: 8s在插入情况下各方法所耗时。t1: 12s t2: 14s t3: 13s t4: 13s结论插入情况下各方法差不多更新的情况下则差异比较大。方法二T4() 比 方法二T2() 快 20%大家如果有兴趣,可以得用上面的代码,将初始化table1的代码更改一下,比较添加更多的记录到100,000级,或者添加更多的字段进行测试。 如有什么不同结果希望能反馈给我以对此试验进行补充修正。
