True or false access: 4. Comparison between "select * into worksheet from temporary table" and "create table Worksheet (...)" Fast?

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 55 floor ::

What is the create table method? I usually save an empty worksheet as a temporary table,
Use the select * into worksheet from temporary table method.
Slow?
The limitation of this method is that the relationship between tables cannot be deleted.

When I see this conjecture, I feel more skeptical. Because select * into newtable from oldtable, access needs to get the definition of all fields from oldtable before creating the table. If the conjecture is true, it indicates what other access mechanism can be used to directly access the table structure at the underlying layer. However, the SELECT statement can be any query, which is unlikely. Therefore, it is difficult to prove this conjecture through a simple experiment.

Create a new empty T. MDB and create a module. The Code is as follows.

Option compare database <br/> Option explicit </P> <p> Public sub Ti () <br/> dim ssql as string <br/> dim conn as ADODB. connection <br/> set conn = currentproject. connection </P> <p> ssql = "create table Table2 (ID integer, cname char (10)" <br/> Conn. execute ssql </P> <p> end sub <br/> Public sub TX () <br/> dim ssql as string <br/> dim conn as ADODB. connection <br/> set conn = currentproject. connection </P> <p> dim I as integer <br/> on error resume next <br/> for I = 1 to 9000 <br/> ssql = "Drop table t "& (10000 + I) <br/> currentproject. connection. execute ssql <br/> next I <br/> on error goto 0 <br/> end sub </P> <p> Public sub T1 () <br/> dim ssql as string <br/> dim I as integer </P> <p> for I = 1 to 9000 <br/> ssql = "CREATE TABLE t" & (10000 + I) & "(ID integer, cname char (10)" <br/> currentproject. connection. execute ssql <br/> next I <br/> end sub </P> <p> Public sub T2 () <br/> dim ssql as string <br/> dim I as integer </P> <p> for I = 1 to 9000 <br/> ssql = "select * into t "& (10000 + I) & "from Table2" <br/> currentproject. connection. execute ssql <br/> next I <br/> end sub </P> <p> Public Sub T () </P> <p> call TX <br/> debug. print "T1 start. ", now <br/> call t1 <br/> debug. print "T1 end. ", now </P> <p> call TX <br/> debug. print "T2 start. ", now <br/> call t2 <br/> debug. print "T2 end. ", now <br/> end sub </P> <p>

Step 1: run TI () to create an original table Table2 for use by the following select * into newtable from oldtable.
Step 2: Run T () and the result is as follows.
T1 start. 5/23/2009 3:06:54
T1 end. 5/23/2009 3:07:03
T2 start. 5/23/2009 3:07:17
T2 end. 5/23/2009 3:07:29

T1 () 9 s, T2 () 12 s

 

Conclusion: conjecture is not true.