ACCESS的真假:二、检查记录有无再insert 或 update 比 不管有无直接 d...
比如表 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()
-
-
- 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()
-
-
- 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()
-
-
- 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()
-
-
- 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" 这一句注释的情况下,也就是表中存在该记录。需要进行更新。
表中记录已存在 测试结果:
t1 start. 5/14/2009 12:02:36 PM
t1 end . 5/14/2009 12:02:42 PM
t2 start. 5/14/2009 12:02:42 PM
t2 end . 5/14/2009 12:02:52 PM
t3 start. 5/14/2009 12:02:52 PM
t3 end . 5/14/2009 12:02:57 PM
t4 start. 5/14/2009 12:02:57 PM
t4 end . 5/14/2009 12:03:05 PM
把这个注释找开,即模拟记录不存在的情况。
表中记录不存在 测试结果:
t1 start. 5/14/2009 12:14:22 PM
t1 end . 5/14/2009 12:14:34 PM
t2 start. 5/14/2009 12:14:34 PM
t2 end . 5/14/2009 12:14:48 PM
t3 start. 5/14/2009 12:14:48 PM
t3 end . 5/14/2009 12:15:01 PM
t4 start. 5/14/2009 12:15:01 PM
t4 end . 5/14/2009 12:15:14 PM
在更新情况下各方法所耗时。t1: 6s t2: 10s t3: 5s t4: 8s
在插入情况下各方法所耗时。t1: 12s t2: 14s t3: 13s t4: 13s
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。