ACCESS的真假：一、DROP删除表再重建比Delete from Table1快吗？
2009-10-15 08:46:42
Option Explicit
Public 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 i
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 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 Sub
Public 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
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 1000
ssql = "delete from t" & (10000 + i)
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
测试结果
t1 start. 5/15/2009 8:03:12 PM t1 end . 5/15/2009 8:03:16 PM t2 start. 5/15/2009 8:03:31 PM t2 end . 5/15/2009 8:03:33 PM
测试结论 T2() 耗时 < T1() 耗时, 测试结果再一次与猜想背离。
