True and false ACCESS: 1. Is it faster to DROP and delete a table and then recreate it than DeletefromTable1?

Sometimes, when we need to delete all the records in the table, in many database systems, we will habitually choose to use truncatetable for implementation. In many DBMS, truncatetable considers DDL statements, it is equivalent to droptable and then createtable. speed efficiency is generally considered much higher than deletefromtable. Although Dropta

Sometimes, when we need to delete all the records in the table, in many database systems, we will habitually choose to use truncate table for implementation. In many DBMS, truncate table is considered a DDL statement, equivalent to drop table and then create table. the speed efficiency is generally considered much higher than that of the delete from table. Although Drop ta

Sometimes, when we need to delete all the records in the table, in many database systems, we will habitually choose to use truncate table for implementation. In many DBMS, truncate table is considered a DDL statement, equivalent to drop table and then create table. the speed efficiency is generally considered much higher than that of the delete from table.

Although Drop table and delete are not comparable, some of them are similar to comparison with insert and select, there is no alternative between them. Drop + create can only be used with the delete from tbl; function, and there is no foreign key reference in this table. In the actual system, it is rare to design all deletion scenarios that require delete from tbl without any conditions. Only temporary tables can be used to process data in some cases. However, since temporary tables are used up, they are usually deleted after use, and delete from tbl is not required;

I still feel skeptical about this point of view, so I did a test to prove it.

Verify: DROP in ACCESS to Delete a table and recreate it faster than Delete from Table1

However, the following test scheme is designed. There are 10 thousand records in Table 1. (if it does not affect the test results, you can adjust the results by yourself .) Then copy the table to 1000. Use drop/create and delete to compare and test. Since the comparison test is performed on the same machine, theoretically in other different environments (different CPU speed, different memory ,...) It should be the same relative result.

Option Compare DatabaseOption ExplicitPublic Sub ti () Dim ssql As String Dim conn As ADODB. connection Set conn = CurrentProject. connection ssql = "create table table1 (id integer, cname char (10)" conn. execute ssql Dim I As Integer For I = 1 To 10000 ssql = "insert into table1 (id, cname) values (" & I & ", '" & I &"') "conn. execute ssql DoEvents Next iEnd SubPublic Sub tx () Dim ssql As String Dim conn As ADODB. connection Set conn = CurrentProject. connection Dim I As Integer On Error Resume Next For I = 1 To 1000 ssql = "drop table t" & (10000 + I) CurrentProject. connection. execute ssql Next I On Error GoTo 0 For I = 1 To 1000 ssql = "select * into t" & (10000 + I) & "from table1" conn. execute ssql DoEvents Next I End SubPublic Sub t1 () Dim ssql As String Dim I As Integer For I = 1 To 1000 ssql = "drop table t" & (10000 + I) CurrentProject. connection. execute ssql = "create table t" & (10000 + I) & "(id integer, cname char (10)" CurrentProject. connection. execute ssql Next iEnd SubPublic Sub t2 () Dim ssql As String Dim I As Integer For I = 1 To 1000 ssql = "delete from t" & (10000 + I) CurrentProject. connection. execute ssql Next iEnd SubPublic Sub t () Call tx Debug. print "t1 start. ", Now Call t1 Debug. print "t1 end. ", Now Call tx Debug. print "t2 start. ", Now Call t2 Debug. print "t2 end. ", NowEnd Sub

Test Results

T1 start. 5/15/2009 8:03:12
T1 end. 5/15/2009 8:03:16
T2 start. 5/15/2009 8:03:31
T2 end. 5/15/2009 8:03:33

Test conclusion
The time consumed by T2 () is <T1 (), and the test result deviates from the Conjecture again.