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

下面这个贴子中讨论了很多ACCESS中的一些猜想假设。

Access或其它数据库操作效率问题.欢迎讨论

http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor

比如表 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 <div align="left"> <script type="text/javascript"><!-- google_ad_client = "pub-4084631125052197"; /* 468x60, 创建于 09-1-6 */ google_ad_slot = "7437767437"; google_ad_width = 468; google_ad_height = 60; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> </div> <div style="float: left;margin: 1px;"> <script type="text/javascript"><!-- google_ad_client = "pub-4084631125052197"; /* 468x15, 创建于 09-4-11 */ google_ad_slot = "4877078008"; google_ad_width = 468; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> </div> <p align="center" class="pageLink"><a title="Page"> <b>1</b>/<b>2</b> </a>   <b>1</b> <a href="/html/database/2009-05/1102_2.html">2</a> <a href="/html/database/2009-05/1102_2.html">下一页</a> <a href="/html/database/2009-05/1102_2.html">尾页</a></p></td> </tr> </table> <div class="ebeb">如果您需转载 ACCESS的真假：二、检查记录有无再insert 或 update 比 不管有无直接 delete 再 insert 快吗？，请注明来自LinuxGoo.com，其版权归原作者所有。请广大网友留言时遵纪守法，使用文明用语。如果您在应用中有什么问题，请在下面留言，我们会尽快解答。</div> <table border="0" align="center" cellpadding="0" cellspacing="8"> <tr> <td><table border="0" align="center" cellpadding="0" cellspacing="0" class="digg"> <tr> <td class="diggnum" id="diggnum"><strong><script type="text/javascript" src="/e/public/ViewClick?classid=3&id=1102&down=5"></script></strong></td> </tr> <tr> <td class="diggit"><a href="JavaScript:makeRequest('/e/public/digg?classid=3&id=1102&dotop=1&doajax=1&ajaxarea=diggnum','EchoReturnedText','GET','');">来顶一下</a></td> </tr> </table></td> <td><table border="0" align="center" cellpadding="0" cellspacing="0" class="digg"> <tr> <td valign="middle" class="diggnum"><strong><a href="/"><img src="/skin/default/images/back.gif" alt="近回首页" width="12" height="13" border="0" align="absmiddle" /></a></strong></td> </tr> <tr> <td class="diggit"><a href="/">返回首页</a></td> </tr> </table></td> </tr> </table> </td> </tr> </table> <script> function CheckPl(obj) { if(obj.saytext.value=="") { alert("您没什么话要说吗？"); obj.saytext.focus(); return false; } return true; } </script><form action="/e/enews/index.php" method="post" name="saypl" id="saypl" onsubmit="return CheckPl(document.saypl)"> <table width="100%" border="0" cellpadding="0" cellspacing="0" id="plpost"> <tr> <td><table width="100%" border="0" cellpadding="0" cellspacing="0" class="title"> <tr> <td><strong>发表评论</strong></td> <td align="right"><a href="/e/pl?classid=3&id=1102">共有<span><script type="text/javascript" src="/e/public/ViewClick?classid=3&id=1102&down=2"></script></span>条评论</a></td> </tr> </table> <table width="100%" border="0" cellspacing="10" cellpadding="0"> <tr> <td><table width="100%" border="0" cellpadding="0" cellspacing="2"> <tr> <td width="56%" align="left">用户名: <input name="username" type="text" class="inputText" id="username" value="" size="16" /></td> <td width="44%" align="left">密码: <input name="password" type="password" class="inputText" id="password" value="" size="16" /></td> </tr> <tr> <td align="left">验证码: <input name="key" type="text" class="inputText" size="10" /> <img src="/e/ShowKey?ecms" align="absmiddle" /> </td> <td align="left"><input name="nomember" type="checkbox" id="nomember" value="1" checked="checked" /> 匿名发表</td> </tr> </table> <textarea name="saytext" rows="6" id="saytext">