True or false access: 2. Check whether the record is inserted or updated. Is it faster than directly delete or insert?

The following post discusses some conjecture assumptions in access.

Access or other database operation efficiency issues. Welcome to discuss

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

 

Reply to reference wwwwb on the 9th floor ::

You can test it by yourself, but based on experience,
Delete, insert again (the data volume is larger than the number of records not inserted), and then index creation, the speed is relatively slow.

For example, table 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 |
|... |
+ ---- + ------- +

 

If there is a record (1234, 'kkkk '), you need to add it. There are two methods.

Method 1: First judge whether record 1234 exists. If yes, update Table1 set cname = 'kkkk 'Where id = 1234; otherwise, insert into Table1 values (1234, 'kkkkk ');

Method 2: Directly Delete from Table1 where id = 1234; insert into Table1 values (1234, 'kkkk ') without any judgment ');

 

Which method is faster?

Before the test, it seems that the second method is slow to delete and add records. But just by feeling, what is actually it, so I did a simple test.

Access 2003 SP3 + Windows 2000 SP4

Create an empty T. MDB file and Create Table Table1 (ID int primary key, cname varchar (10)

Create a module with the following code.

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, '123 ') "<br/> else <br/> ssql =" Update Table1 set cname = '000000' 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 = '000000' where id = 1234 "<br/> currentproject. connection. execute ssql, naffectedrow </P> <p> If naffectedrow = 0 then <br/> ssql = "insert into Table1 values (1234, '123 ') "<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, '000000')" <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>

The results are interesting. This record exists in the 'currentproject. Connection. Execute "delete from Table1 where id = 1234" clause. Update required.

Test results already exist in the table:
T1 start. 5/14/2009 12:02:36
T1 end. 5/14/2009 12:02:42
T2 start. 5/14/2009 12:02:42
T2 end. 5/14/2009 12:02:52
T3 start. 5/14/2009 12:02:52
T3 end. 5/14/2009 12:02:57
T4 start. 5/14/2009 12:02:57
T4 end. 5/14/2009 12:03:05

 

Open this comment to simulate the case where the record does not exist.

Test results are not found in the table:
T1 start. 5/14/2009 12:14:22
T1 end. 5/14/2009 12:14:34
T2 start. 5/14/2009 12:14:34
T2 end. 5/14/2009 12:14:48
T3 start. 5/14/2009 12:14:48
T3 end. 5/14/2009 12:15:01
T4 start. 5/14/2009 12:15:01
T4 end. 5/14/2009 12:15:14

 

The time consumed by each method during update. T1: 6 s t2: 10 s T3: 5S T4: 8 s
The time consumed by each method during insertion. T1: 12 s t2: 14 s T3: 13 s T4: 13 s

 

Conclusion

The methods are similar in the case of insertion.

In the case of update, the difference is relatively large.Method 2 T4 () is 20% faster than method 2 T2 ()

 

If you are interested, you can use the above Code to change the code that initializes Table1, add more records to level 100,000, or add more fields for testing. If you have any different results, you may wish to report them to me for additional amendments to this experiment.