ACCESS的真假：二、檢查記錄有無再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)

建立一個模組，代碼如下。

Option Compare Database<br />Option Explicit</p><p>Public Sub tx()</p><p> Dim i As Integer<br /> For i = 1 To 10000<br /> CurrentProject.Connection.Execute "insert into table1 values(" & i & ",'" & i & "')"<br /> Next<br />End Sub</p><p>Public Sub t1()<br /> 'CurrentProject.Connection.Execute "delete from table1 where id=1234"</p><p> Dim rs As New ADODB.Recordset<br /> Dim ssql As String<br /> ssql = "select * from table1 where id=1234"<br /> rs.Open ssql, CurrentProject.Connection, adOpenStatic, adLockOptimistic<br /> If rs.EOF Then<br /> rs.AddNew<br /> rs.Fields("id").Value = 1234<br /> End If<br /> rs.Fields("cname").Value = "KKK"<br /> rs.Update<br /> rs.Close<br />End Sub</p><p>Public Sub t2()<br /> 'CurrentProject.Connection.Execute "delete from table1 where id=1234"</p><p> Dim rs As New ADODB.Recordset<br /> Dim ssql As String<br /> ssql = "select * from table1 where id=1234"<br /> rs.Open ssql, CurrentProject.Connection<br /> If rs.EOF Then<br /> ssql = "insert into table1 values(1234,'1234')"<br /> Else<br /> ssql = "update table1 set cname='1234' where id=1234"<br /> End If<br /> rs.Close<br /> CurrentProject.Connection.Execute ssql<br />End Sub</p><p>Public Sub t3()<br /> 'CurrentProject.Connection.Execute "delete from table1 where id=1234"</p><p> Dim ssql As String<br /> Dim nAffectedRow As Long</p><p> ssql = "update table1 set cname='1234' where id=1234"<br /> CurrentProject.Connection.Execute ssql, nAffectedRow</p><p> If nAffectedRow = 0 Then<br /> ssql = "insert into table1 values(1234,'1234')"<br /> CurrentProject.Connection.Execute ssql, nAffectedRow<br /> End If</p><p>End Sub</p><p>Public Sub t4()<br /> 'CurrentProject.Connection.Execute "delete from table1 where id=1234"</p><p> Dim ssql As String</p><p> ssql = "delete from table1 where id=1234"<br /> CurrentProject.Connection.Execute ssql</p><p> ssql = "insert into table1 values(1234,'1234')"<br /> CurrentProject.Connection.Execute ssql</p><p>End Sub</p><p>Public Sub t()<br /> Dim i As Integer<br /> Dim nCnt As Integer<br /> nCnt = 5000</p><p> Debug.Print "t1 start.", Now<br /> For i = 1 To nCnt<br /> Call t1<br /> Next i<br /> Debug.Print "t1 end .", Now</p><p> Debug.Print "t2 start.", Now<br /> For i = 1 To nCnt<br /> Call t2<br /> Next i<br /> Debug.Print "t2 end .", Now</p><p> Debug.Print "t3 start.", Now<br /> For i = 1 To nCnt<br /> Call t3<br /> Next i<br /> Debug.Print "t3 end .", Now</p><p> Debug.Print "t4 start.", Now<br /> For i = 1 To nCnt<br /> Call t4<br /> Next i<br /> Debug.Print "t4 end .", Now</p><p>End Sub</p><p>

結果很有趣。在把  '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級，或者添加更多的欄位進行測試。 如有什麼不同結果希望能反饋給我以對此實驗進行補充修正。