Office中国论坛/Access中国论坛

标题: “生成表”与“删除记录”再追加记录，那种方式速度快？ [打印本页]

作者: huangxiuwen 时间: 2009-3-20 12:02
标题: “生成表”与“删除记录”再追加记录，那种方式速度快？
请教各位大侠：
想把查询出来的记录作为临时表。请问以下两种方式，那种方式在速度上更快一些。
第1种方式：把查询出来的记录，以生成表的方式直接复盖掉临时表。
第2种方式：①：先删除掉临时表中所有的记录。②：再把查询出来的记录追加到临时表中。

先感谢大家。

作者: ACMAIN_CHM 时间: 2009-3-20 13:07

第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 时间: 2009-3-20 16:08
感谢ACMAIN_CHM

作者: tz-chf 时间: 2009-3-20 16:55
方法2常规，如果是给客户用的，方法1不考虑，因为涉及到权限

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)