“生成表”与“删除记录”再追加记录，那种方式速度快？

huangxiuwen

请教各位大侠：
想把查询出来的记录作为临时表。请问以下两种方式，那种方式在速度上更快一些。
第1种方式：把查询出来的记录，以生成表的方式直接复盖掉临时表。
第2种方式：①：先删除掉临时表中所有的记录。②：再把查询出来的记录追加到临时表中。


先感谢大家。

ACMAIN_CHM

第2种方式 快

试验环境及代码。windwos2000 SP4 + Access 2003 SP3, 关闭无关应用程序。
新建空MDB文件，创建模块如下。

Option Compare Database
Option Explicit

Public Sub createEnv()
    Dim sSQL As String
    Dim i As Integer
   
    sSQL = "create table t( id  integer constraint pk_t primary key, f1 integer)"
    CurrentProject.Connection.Execute sSQL
   
    sSQL = "create table t_n( id  integer constraint pk_t primary key, f1 integer)"
    CurrentProject.Connection.Execute sSQL
   
    For i = 1 To 10000
        sSQL = "insert into t values(" & i & "," & i * 2 & ")"
        CurrentProject.Connection.Execute sSQL
    Next i
   
End Sub

Public Sub t1() 'test inset
    Dim sSQL As String
    Dim i As Integer
   
    Debug.Print Now(), "INSERT Started..."
   
    For i = 1 To 10000
        sSQL = "delete from t"
        CurrentProject.Connection.Execute sSQL
        sSQL = "insert into t_n select * from t"
        CurrentProject.Connection.Execute sSQL
    Next i
   
    Debug.Print Now(), "INSERT Accomplished..."
   
End Sub

Public Sub t2() 'test select into
    Dim sSQL As String
    Dim i As Integer
   
    Debug.Print Now(), "SELECT INTO Started..."
   
    For i = 1 To 10000
        sSQL = "drop table t_n"
        CurrentProject.Connection.Execute sSQL
        sSQL = "select * into t_n from t"
        CurrentProject.Connection.Execute sSQL
    Next i
   
    Debug.Print Now(), "SELECT INTO Accomplished..."
End Sub



执行 createEnv 创建测试表,
交叉执行 T1(), T2(),T(2),T(1)  以避免先后次序的影响。结果如下


3/20/2009 12:57:52 PM       INSERT Started...
3/20/2009 12:58:07 PM       INSERT Accomplished...
3/20/2009 12:58:20 PM       SELECT INTO Started...
3/20/2009 12:58:40 PM       SELECT INTO Accomplished...

3/20/2009 12:59:35 PM       SELECT INTO Started...
3/20/2009 12:59:57 PM       SELECT INTO Accomplished...
3/20/2009 1:00:02 PM        INSERT Started...
3/20/2009 1:00:18 PM        INSERT Accomplished...




******************
*  一切皆有可能  *
******************
.
ACMAIN - Access论坛回贴准则（个人）.
.
同一贴子不回复第二次
.
QQ群 48866293 / 12035577 / 7440532 / 13666209
http://www.office-cn.net/vvb/ .
http://www.accessbbs.cn/bbs/index.php .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
.
http://www.office-cn.net/home/space.php?uid=141646 .

huangxiuwen

感谢ACMAIN_CHM

tz-chf

方法2常规，如果是给客户用的，方法1不考虑，因为涉及到权限