True or false ACCESS: 3. Insert 0.1 million records to a table at a speed less than 10 thousand

The following post discusses some conjecture assumptions in ACCESS. Reply # replyachor reference the reply from wwwwb on the 28 th floor: for example, insert 0.1 million records and insert 10 thousand records at a speed

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 reference 28 floor wwwwb reply: for example, insert 0.1 million records, speed Insert 10 thousand

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 28 th floor ::
For example, insert 0.1 million records at a speed <插入1万条记录；< fieldset>

In the face of this problem, the first idea is that inserting too many records into an mdb file will naturally lead to an increase in the MDB file, and you need to constantly apply for disk space allocation from the operating system. For some reason, the speed may be slow. However, it seems that there is no reason to think about it again. After all, adding 10000 records also requires a space of 10000 records. The speed should not be affected. After all, ACCESS does not use any redo/undo log.

Then a simple test is conducted as follows to prove this conjecture.

Create an empty t. mdb file and Create Table table1 (id int primary key, cname varchar (10)

Create a new module. The content is as follows.

Option Compare DatabaseOption ExplicitPublic Sub t1 (nRowCnt As Long) Dim I As Long Dim conn As ADODB. connection Set conn = CurrentProject. connection For I = 1 To nRowCnt conn. execute "insert into table1 (id, cname) values (" & I & ", '" & I & "')" Next I End SubPublic Sub t () CurrentProject. connection. execute "delete from table1" Debug. print "t10000 start. ", Now Call t1 (10000) Debug. print "t10000 end. ", Now CurrentProject. connection. execute "delete from table1" Debug. print "t100000 start. ", Now Call t1 (100000) Debug. print "t100000 end. ", Now End Sub

The result of running t () is as follows:
T10000 start. 5/14/2009 7:53:10 PM
T10000 end. 5/14/2009 7:53:29 PM
T100000 start. 5/14/2009 7:53:29 PM
T100000 end. 5/14/2009 7:56:06 PM

T10000. 19 s
T100000. 157 s

Conclusion:

The total time of inserting 0.1 million entries is obviously longer than inserting 10 thousand (157 s> 19 s), but the speed is obviously faster (157/100000 <19/10000)

It seems that practice is the only criterion for testing.