ACCESS的真假：四、SELECT * INTO 工作表 FROM 臨時表 比 CREATE TABLE 工作表 (...) 速度快?

vIEw plaincopy to clipboardprint?
Option Compare Database  
Option Explicit  
 
Public Sub ti()  
    Dim ssql As String 
    Dim conn As ADODB.Connection  
    Set conn = CurrentProject.Connection  
      
    ssql = "create table table2(id integer,cname char(10))" 
    conn.Execute ssql  
      
End Sub 
Public 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 9000  
        ssql = "drop table t" & (10000 + i)  
        CurrentProject.Connection.Execute ssql  
    Next i  
    On Error GoTo 0  
End Sub 
 
Public Sub t1()  
    Dim ssql As String 
    Dim i As Integer 
      
    For i = 1 To 9000  
        ssql = "create table t" & (10000 + i) & " (id integer,cname char(10))" 
        CurrentProject.Connection.Execute ssql  
    Next i  
End Sub 
 
Public Sub t2()  
    Dim ssql As String 
    Dim i As Integer 
      
    For i = 1 To 9000  
        ssql = "select * into t" & (10000 + i) & " from table2" 
        CurrentProject.Connection.Execute ssql  
    Next i  
End Sub 
 
Public 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  .", Now  
End Sub 
Option Compare Database
Option Explicit

Public Sub ti()
    Dim ssql As String
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
   
    ssql = "create table table2(id integer,cname char(10))"
    conn.Execute ssql
   
End Sub
Public 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 9000
        ssql = "drop table t" & (10000 + i)
        CurrentProject.Connection.Execute ssql
    Next i
    On Error GoTo 0
End Sub

Public Sub t1()
    Dim ssql As String
    Dim i As Integer
   
    For i = 1 To 9000
        ssql = "create table t" & (10000 + i) & " (id integer,cname char(10))"
        CurrentProject.Connection.Execute ssql
    Next i
End Sub

Public Sub t2()
    Dim ssql As String
    Dim i As Integer
   
    For i = 1 To 9000
        ssql = "select * into t" & (10000 + i) & " from table2"
        CurrentProject.Connection.Execute ssql
    Next i
End Sub

Public 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  .", Now
End Sub

步驟一：運行 ti()創建一個原表 table2 以供後面的 select * into newtable from oldTable 使用。
步驟二：運行 t() 結果如下。
t1 start.     5/23/2009 3:06:54 PM
t1 end  .     5/23/2009 3:07:03 PM
t2 start.     5/23/2009 3:07:17 PM
t2 end  .     5/23/2009 3:07:29 PM

t1() 9s , t2() 12s