access的真假：二、检查记录有无再insert 或 update 比 不管有无直接 delete 再 insert 快吗？

时　间:2009-09-10 09:55:49
作　者:ACMAIN_CHM   ID：3535  城市：无锡
摘　要:检查记录有无再insert 或 update 比 不管有无直接 delete 再 insert 快?
正　文:

下面这个贴子中讨论了很多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)

新建一个模块，代码如下。

view plaincopy to clipboardprint?
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 
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" 这一句注释的情况下，也就是表中存在该记录。需要进行更新。

表中记录已存在 测试结果：
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

结论

插入情况下各方法差不多

更新的情况下则差异比较大。方法二T4() 比 方法二T2() 快 20%

大家如果有兴趣，可以得用上面的代码，将初始化table1的代码更改一下，比较添加更多的记录到100,000级，或者添加更多的字段进行测试。 如有什么不同结果希望能反馈给我以对此试验进行补充修正。